Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Server 2008 running slow

Author  Topic 

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 12:50:36
I have just transferred a database from SQL Server 2005 to 2008. I detached the database from one and attached to the other.

I am connecting to the database with Windows forms using vb.net Visual studio 2010. The connection strings are ADO and OLEdb. When it was on 2005 it run quite smooth with no noticable delays in opening forms. Since I transferred it it is very slow.

How can I test or compare the speed of the two servers?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 13:05:28
Did you run UPDATE STATISTICS with fullscan? Have you run profiler or PerfMon to determine where the bottleneck is?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 13:05:44
Did you switch hardware?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 13:12:53
No I didn't swtich hardware. It sits on the same server.
I have not run any tests as I am not sure how to do this and what is the best one to start on.

I am not a SQL admin person so forgive me ignorance in this area.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 13:27:06
Run update stats with fullscan on each of your tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 14:18:46
Ok many thanks for that I will make a start. There is about 40 tables so that may take a bit of time.

By doing this should I expects to see anything in the Results panel or is this reformatting the tables in some way.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 14:21:47
It'll just succeed, don't think there's any output.

It's updating the statistics for the tables, which will help the query optimizer make decisions on what query plan to use. Check out UPDATE STATISTICS in Books Online for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 14:31:20
I will report back tomorrow.
Thanks again.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 14:44:40


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 14:45:49
Also, does your app support SQL Server 2008 compatibility level? When you upgrade from 2005 to 2008, it keeps the database in 2005 mode. If your app supports it, switch the compatibility level to 100 which is 2008 mode.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 14:46:00
That was quicker than I thought. Does not seemed to have improved the load speed much though.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 14:54:36
Next up is identifying the culprit. You'll need to run a trace to capture the load, and I'd recommend running PerfMon too.

For the trace, I'd recommend these events: RPC:Completed, SP:Completed, SQL:Stmt Completed, SQL:Batch Completed.

Do not run profiler on the server, I'd recommend running a server-side trace instead of the GUI actually, but I'm not sure of your experience level. If you are going to run profiler to capture it, run it on a test server and only capture it to a file. Do not capture it to a table. Once you've captured the data, stop the trace and then and only then save it to a table. Do not put this table on the prod server, save it to a test server.

Let me know once you've captured the trace and imported the data to a test server. I can help you from there.

Regarding PerfMon, I'd watch the disks, CPU, and page life expectancy for starters.

Also, you need to check wait stats and blocking.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 15:10:11
Hi Tara,

My SQL experience level is low so I will have to check those recommendation out before moving forward.

One other thing I should mention that may be clue to the cause of the sluggishness is that in order to connect I had to increase the timeout on my connection string to 120 on my application. This was not needed before as it would connect within a matter of seconds.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 15:13:26
120! Oh my. You've got some serious issues then, likely serious locking. Is there someone with more experience that can guide you through this? Going back and forth on the Internet will mean it's going to take at least a couple of days to tackle this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-25 : 15:28:11
I have just adjusted to 30 and it will open. If I set it to 20 it falls over.

I am on my own on this one for about 4 weeks before anyone comes in here with SQL Server skills. So I think I have a few late nights ahead.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 15:47:16
How long ago was the upgrade? And when did you last restart? I'm not suggesting you restart it, I need the answer for my next steps.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 15:48:23
Depending on your answer, run these and show us the output:


WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO


Switch to the upgraded database and then run:


SELECT
[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
[Table] = [statement],
[CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
+ ' ON '
+ [statement]
+ ' ( ' + IsNull(mid.equality_columns, '')
+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE
CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END
+ mid.inequality_columns END + ' ) '
+ CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END
+ ';',
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-26 : 01:47:53
1) Are the files sitting on the same drive layout as before? i.e dat,log,tempdb
2) Have you analysed the query on the timeout? Have you checked the Execution Plan?
3)Is there any anti-virus ?
4)Check for high levels of blocks\deadlocks

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-27 : 04:24:42
I have run those scripts and the results are as follows:

On SQL Server 2005
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
SERVER_IDLE_CHECK 22.00 22.00 0.00 2 70.05 10.9980 10.9980 0.0000
ASYNC_NETWORK_IO 4.10 3.95 0.16 279 13.06 0.0147 0.0141 0.0006
PAGEIOLATCH_SH 2.14 2.14 0.00 688 6.81 0.0031 0.0031 0.0000
IO_COMPLETION 0.86 0.86 0.00 185 2.73 0.0046 0.0046 0.0000
CHKPT 0.66 0.66 0.00 1 2.09 0.6550 0.6550 0.0000
MSQL_XP 0.50 0.50 0.00 15 1.59 0.0333 0.0333 0.0000

And on SQL Server 2008 (the updgraded server)
It returns no results they are blank.
Go to Top of Page

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-06-27 : 04:46:35
Hi Jack,

quote:

1) Are the files sitting on the same drive layout as before? i.e dat,log,tempdb
2) Have you analysed the query on the timeout? Have you checked the Execution Plan?
3)Is there any anti-virus ?
4)Check for high levels of blocks\deadlocks



1) Files are on the same drive. I simply detached the database from SQL 2005 and then attached it to 2008.
2) Nothing has changed here from before and that worked fine.
3)Again yes but the same as before
4)I am not sure how I go about this and what I should be lookingfor in the results. Should I run sp_lock and if so what am I looking for?

Thanks for your help.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-28 : 01:54:19
The DMVs - sys.dm_exec_requests , sys.dm_exec_sql_text , supply you with various details regarding blocking. Blocking can be normal on a SQL Server - depending on lock types used.You're looking for excessive blocking that may give you a hint about either non-optimised queries or some other bottlneck , such as slow Disk IO

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
    Next Page

- Advertisement -