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 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2012-06-25 : 14:31:20
|
I will report back tomorrow.Thanks again. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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. |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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_SFROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNumGROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.PercentageHAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage thresholdGO 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_columnsFROM 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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,tempdb2) 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\deadlocksJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
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 2005WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_SSERVER_IDLE_CHECK 22.00 22.00 0.00 2 70.05 10.9980 10.9980 0.0000ASYNC_NETWORK_IO 4.10 3.95 0.16 279 13.06 0.0147 0.0141 0.0006PAGEIOLATCH_SH 2.14 2.14 0.00 688 6.81 0.0031 0.0031 0.0000IO_COMPLETION 0.86 0.86 0.00 185 2.73 0.0046 0.0046 0.0000CHKPT 0.66 0.66 0.00 1 2.09 0.6550 0.6550 0.0000MSQL_XP 0.50 0.50 0.00 15 1.59 0.0333 0.0333 0.0000And on SQL Server 2008 (the updgraded server)It returns no results they are blank. |
|
|
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,tempdb2) 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 before4)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. |
|
|
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 IOJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
Next Page
|