January 28, 2008 at 3:32 am
Hi Guys,
Need some advice on something. I have a table that has indexes on a different filegroup, the clustered index is the only one thats on the same drive as table. The user has complained that they are getting slow responses when doing a select on a table.
What can be done to make performance better. Should the indexes be put on another filegroup or should the indexes be on the same filegroup as the table.
Please help.
Regards
IC
January 28, 2008 at 5:32 am
There's no problem having the nonclustered indexes on a different file group from the clustered index (which is actually the table). If the file groups are on different drives, it may provide a performance improvement.
Can you post the slow query and the table structure (with indexes)? Also, check to make sure there isn;t blocking. If the query's running, you can check the sys.dm_db_exec_requests to see what the query's waiting for (wait time and wait type)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 5:36 am
And be sure to check the execution plan to be sure that indexes are being used at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2008 at 5:39 am
select top 10 * from [tablename] with (nolock)
where [Indexcolumnname] > '2008-01-01'
It takes 11 minutes to execute this query.
January 28, 2008 at 6:00 am
And the execution plan?
Plus, how often does the index get rebuilt or the statistics get updated?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2008 at 6:28 am
Am busy running DBCC ShowContig at the moment. Taking quite long.
Checked out execution plan and its shows this:
Query Cost (relative to batch) : 100%
Estimated Operator Cost: 99 %
Estimated I/O Cost : 12533.5
Estimated CPU Cost : 1168.34
Can anyone explain to me what this exactly means. I'm quite new at these performance issues.
Many Thanks
IC
January 28, 2008 at 7:43 am
Imke Cronje (1/28/2008)
Am busy running DBCC ShowContig at the moment. Taking quite long.Checked out execution plan and its shows this:
Query Cost (relative to batch) : 100%
Estimated Operator Cost: 99 %
Estimated I/O Cost : 12533.5
Estimated CPU Cost : 1168.34
Can anyone explain to me what this exactly means. I'm quite new at these performance issues.
Many Thanks
IC
Query Cost is simply the entire query within the batch that you called. If you had more than one select statement, you'd see different numbers for each, totalling 100%.
Operator Cost is the cost of the operation. The real question on this particular operation, which is quite costly as you can see from the I/O & CPU, what operation is it? Was it a clustered index scan, an index scan, a seek, a table scan? It's hard to say what's going on. One thing you can look for, is the estimated number of rows compared to the actual number of rows that the query returns. If these values are radically different, you've probably got a statistics issue or it's performing large scans, such as a clustered index or table scan, in order to find the values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2008 at 7:45 am
Can we see the table schema, indexes and the full execution plan please. Is nearly impossible to say anything for sure without those.
Since you're on SQL 2005 (I assume so from the forum) you can save the execution plan as a .sqlplan file. Zip that file and attach it to your post.
How many rows in the table? How many will be returned by the query you gave (if the top 10 wasn't there)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2008 at 10:17 am
Imke Cronje (1/28/2008)
Am busy running DBCC ShowContig at the moment. Taking quite long.Checked out execution plan and its shows this:
Query Cost (relative to batch) : 100%
Estimated Operator Cost: 99 %
Estimated I/O Cost : 12533.5
Estimated CPU Cost : 1168.34
Can anyone explain to me what this exactly means. I'm quite new at these performance issues.
Many Thanks
IC
Hire a pro to help you with your current performance issues while mentoring you to be better at finding/resolving them. Hunting and pecking on a forum without knowing more initially will be a long and painful process. 🙂
So many things can result in a given query running slowly at a given point in time: stats, missing indexes, blocking, any number of resources could be saturated (cpu, I/O, network, etc.), parameter sniffing/poor plan cache ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply