March 17, 2009 at 2:17 pm
We have a client who is complaining about really slow response time during a process. There is a table that is updated during the process that takes 45 seconds to update. I'm not sure if it is the culprit but this is what I know.
1) When I go in to Query Analyzer and perform a select count(*) on that table, it takes 29 seconds to return.
2) If I run the query again immediately after, it is faster and gets faster every time.
3) There are about 70,000 rows in that table.
4) I ran this on the table yesterday which didn't help:
update statistics my_table with fullscan
exec sp_recompile my_table
5) The table has about 140 columns.
6) There are 5 columns that are not null.
7) There is an index using these 5 columns and these values are checked:
March 17, 2009 at 2:21 pm
Can you provide the full scripts for table and index creation, along with the query that is taking a long time. Also, if you could post the Actual Execution plan zipped and attached, that would help us help you. We will be able to see what is long to run in the query, and give you advice on what to do.
Hope it helps,
Cheers,
J-F
March 18, 2009 at 12:19 am
Rog Saber (3/17/2009)
We have a client who is complaining about really slow response time during a process. There is a table that is updated during the process that takes 45 seconds to update. I'm not sure if it is the culprit but this is what I know.1) When I go in to Query Analyzer and perform a select count(*) on that table, it takes 29 seconds to return.
2) If I run the query again immediately after, it is faster and gets faster every time.
3) There are about 70,000 rows in that table.
4) I ran this on the table yesterday which didn't help:
update statistics my_table with fullscan
exec sp_recompile my_table
5) The table has about 140 columns.
6) There are 5 columns that are not null.
7) There is an index using these 5 columns and these values are checked:
1-2: Because after the first time the information is cached and can be accessed quickly again.
3: that is not a lot of records your updates should complete in less then 1 second/
4: bad normalization?
7: only one index? Is the index clustered or non-clustered?
If table only has one index and it is non-clustered you have a heap with very large row length this cause cause your updates to take excessive time. If your cluster index is on the 5 columns what are those columns and how often do they update?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 18, 2009 at 7:59 am
7) There is an index using these 5 columns and these values are checked: clustered index, unique values, filegroup - primary, fill factor -90.
If I perform some re-index, how can I test it to see if it worked?
March 18, 2009 at 8:03 am
When you reindex using the management studio, you can see the current index fragmentation. usually, a higher fragmentation percentage is the sign of poor resulting queries.
Can you provide the query you are running on this table which has 70k rows? 70K rows should not be that hassle to update, maybe you need to review the update query.
Cheers,
J-F
March 18, 2009 at 8:21 am
We ran this last night
DBCC DBREINDEX(my_table,' ',90)
sp_recompile my_table
I am just running a select count(*). Before took 29 seconds. Ran a minute ago took 6 seconds.
Here is a main question. We have a lot of other clients on this server. The other applications are experiencing slow response times too in many instances. I'm wondering it it is a metter of server resources?
March 18, 2009 at 3:56 pm
Yes it can be...
look at http://www.simple-talk.com/sql/performance/sql-server-performance-crib-sheet/
To understant where there issue can be.
For this table please run
DBCC SHOWCONTIG('tablename') WITH ALL_INDEXES
and reply with the results here. Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 1:09 pm
DBCC SHOWCONTIG scanning 'mytable' table...
Table: 'mytable'(1800393483); index ID: 1, database ID: 14
TABLE level scan performed.
- Pages Scanned................................: 6573
- Extents Scanned..............................: 826
- Extent Switches..............................: 825
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.52% [822:826]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 4.60%
- Avg. Bytes Free per Page.....................: 335.5
- Avg. Page Density (full).....................: 95.86%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Also I just ran select count(*) from mytable and it took 15 seconds to return 71954
March 19, 2009 at 3:29 pm
Hi Rog,
Index ID = 1 means this is a cluster index; so it eliminates that question. Your fragmentation level is also good. So the next reason it might taking a while to get information back is because of Disk subsystem or the Memory pressure on server.
On one of my servers I can do count(*) from a table with 7Million rows and it comes back in about 3 seconds. So check how is SQL Server memory/OS memory? How is the disk I/O system is there backlog in the disk system that it is taking I/O to get information to SQL Server.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 3:32 pm
"So check how is SQL Server memory/OS memory? How is the disk I/O system is there backlog in the disk system that it is taking I/O to get information to SQL Server."
How can I do this? We don't have a DBA either.
Thank you
March 19, 2009 at 3:54 pm
The link I posted eailer explains this, http://www.simple-talk.com/sql/performance/sql-server-performance-crib-sheet/.
Basic idea is you want to launch Performace Montior on the server and look at counters for Memory, Physical Disk, and SQL Server: Buffer Manager.
Memory:
- Page Faults/sec : Indicates how shuffling the OS is doing between memory and swap file.
- Available MBytes : Microsoft says 25MB, but I like to play safe so I usually check this counter to see if it drops below 25% of my max memory then I start getting worried and start planning/figuring out what I need to do.
Physical Disk:
- %Idle Time: Should stay close to 0 as possible, if it spikes for extended period of times you might have a problem.
- Avg. Disk Read Queue Length: Depends on your HDD design, lets say you have a Raid 1 Configuration with 2 disks then this value should not exceed 4. Basic number of disks*2; very large numbers for this indicates disk contention issues.
- Avg. Disk Write Queue Length: Same as above.
- Disk Read Bytes/Sec and Disk Write Bytes/Sec: shows you the through put of your server.
SQL Server: Buffer Manager
- Buffer Cache Hit Ratio: Should be >= 99% at all times
- Page Life expectancy: should be > 300 all times.
Those are starters for me ne anyhow..
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply