August 2, 2011 at 9:00 am
I have a table in warehouse
Query is as below...
Select * from tablename
Where ID = '123'
ID is the primary key of the table.
Before it used to be very fast now it is very very slow...
I have checked the locks and there are no locks on this table....
As ID is a primary key, it as unique index on the column...
could someone please help me with the situation. what steps to follow to solve the issue....
August 2, 2011 at 9:02 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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
August 2, 2011 at 9:09 am
check the frgamnetion of the table after that if fragmentation of table is more than 40% you need to go for the rebuild the index .
August 2, 2011 at 9:14 am
Subjective Adapts (8/2/2011)
check the frgamnetion of the table after that if fragmentation of table is more than 40% you need to go for the rebuild the index .
Very far on the possible list of suspects.
August 2, 2011 at 9:33 am
thanks for the reply but below are my findings
I have execute the below query
DBCC showcontig('tablename')
and it gets the result as below
- Pages Scanned................................: 13123
- Extents Scanned..............................: 1641
- Extent Switches..............................: 1640
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1641:1641]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 7.13%
- Avg. Bytes Free per Page.....................: 11.3
- Avg. Page Density (full).....................: 99.86%
and executed below query also
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('databasename'),
OBJECT_ID('tablename'), NULL, NULL, NULL);
and the result as below
object_id , index_id, avg_fragmentation_in_percent, page_count
1045578763, 1, 0, 13123,
1045578763, 2, 0, 1873,
1045578763, 3, 0, 2557,
August 2, 2011 at 9:35 am
"very slow query" from Lucky9?
Are you sure you have choosen the right nick? :hehe:
I would suspect that you do insert a lot into your table? Do you have regular DB maintenance plans setup? Try:
UPDATE STATISTICS TableName
It may help...
However previous replies are right: you need to provide table DDL and query plan for get the right answer
August 2, 2011 at 9:35 am
GilaMonster (8/2/2011)
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Fragmentation will have absolutely no effect on a singleton seek (which is what an equality match on a unique index does)
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
August 2, 2011 at 9:36 am
Lucky9 (8/2/2011)
thanks for the reply but below are my findingsI have execute the below query
DBCC showcontig('tablename')
...
Like I said, this was very down the list of possible suspect and now it's gone. Please read the link that Gail posted and follow the instructions in there.
We'll get you on your way as soon as it's done and we have all the info we need.
August 2, 2011 at 9:37 am
Eugene Elutin (8/2/2011)
"very slow query" from Lucky9?Are you sure you have choosen the right nick? :hehe:
I would suspect that you do insert a lot into your table? Do you have regular DB maintenance plans setup? Try:
UPDATE STATISTICS TableName
It may help...
However previous replies are right: you need to provide table DDL and query plan for get the right answer
Nope either. The stats will correctly know that 1 and only 1 row or 0) can be returned so that can't possibly be the issue.
August 2, 2011 at 11:43 pm
Ninja's_RGR'us (8/2/2011)
The stats will correctly know that 1 and only 1 row or 0) can be returned so that can't possibly be the issue.
Well technically it's not the statistics - it's the existence of a unique constraint/index on the column that results in the singleton seek Gail noted. It is hard to imagine a plan where this type of query would be slow, even if the primary key is non-clustered, so I too look forward to seeing it.
http://sqlblog.com/blogs/paul_white/archive/2011/02/17/so-is-it-a-seek-or-a-scan.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 3, 2011 at 1:24 am
SQLkiwi (8/2/2011)
It is hard to imagine a plan where this type of query would be slow, even if the primary key is non-clustered, so I too look forward to seeing it.
Agreed. I'm wondering if someone's dropped the pk and the query is now doing a table scan.
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
August 3, 2011 at 7:19 am
SQLkiwi (8/2/2011)
Ninja's_RGR'us (8/2/2011)
The stats will correctly know that 1 and only 1 row or 0) can be returned so that can't possibly be the issue.Well technically it's not the statistics - it's the existence of a unique constraint/index on the column that results in the singleton seek Gail noted. It is hard to imagine a plan where this type of query would be slow, even if the primary key is non-clustered, so I too look forward to seeing it.
http://sqlblog.com/blogs/paul_white/archive/2011/02/17/so-is-it-a-seek-or-a-scan.aspx
I quickly skimmed the article (sorry but busy morning). I can't find anything about the unique constraint being used over the stats. So if I follow your train of thoughts about the internals, the optimizer will first check for unique constraint and if none is found then it goes to the stats?
My first impression was that the stats would containt info that each key would only have 1 value... hence still having really great estimates. But now that I think of it the former makes a little more sense.
August 3, 2011 at 7:25 am
Ninja's_RGR'us (8/3/2011)
My first impression was that the stats would containt info that each key would only have 1 value... hence still having really great estimates. But now that I think of it the former makes a little more sense.
Yes, the stats will say 1 row, but SQL knows those are estimates, it knows they may be off slightly, it knows that an estimate of 1 could easily return 5 or 10 rows. If all it had were the stats it would have to do a range scan, not a singleton seek. The range scan would only scan one row, so it's more a case of internal semantics and details than anything else.
Bottom line:
estimate of 1 row means probably 1 row or some similar low number
unique constraint and equality match means absolutely one (or zero) row, no possible way it could be more than one
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
August 3, 2011 at 7:28 am
GilaMonster (8/3/2011)
Ninja's_RGR'us (8/3/2011)
My first impression was that the stats would containt info that each key would only have 1 value... hence still having really great estimates. But now that I think of it the former makes a little more sense.Yes, the stats will say 1 row, but SQL knows those are estimates, it knows they may be off slightly, it knows that an estimate of 1 could easily return 5 or 10 rows. If all it had were the stats it would have to do a range scan, not a singleton seek. The range scan would only scan one row, so it's more a case of internal semantics and details than anything else.
Bottom line:
estimate of 1 row means probably 1 row or some similar low number
unique constraint and equality match means absolutely one (or zero) row, no possible way it could be more than one
Love that explaination. Tx 🙂
August 3, 2011 at 8:07 am
Out of left field and most likely not related, but has anything changed on the OS/network side?
I've previously had a mysterious inexplicable slowdown only to discover a disk defrag running. On another occasion a heavy network load caused a delay in results being returned even though the query execution itself was snappy.
I suspect you'll find your answer internal to SQL Server, but I mention external factors in case they get overlooked or need ruling out.
Steve.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply