August 18, 2014 at 3:00 am
Hello,
I've running into some performance issues which I don't really understand... Only one Table of the database is affected....
The Table definition is like:
dbo.test (
col1 [varchar](10) NULL,
col2 [varchar](20) NULL,
col3 [varchar](20) NULL,
col4 [varchar](20) NULL,
col5 [varchar](10) NULL,
col6 [varchar](2) NULL,
col7 [float]
...
col44 [float]
)
The table properties display 284.219 Rows and 28.282,734 MB Data space...
Right now there are no indexes set, which of course will normally be the first problem with any performance issue..
But my problem is, that even an query without any where-clause like
Select top 10 * from dbo.test
takes more than 4 minutes... And i don't understand why??
Here is the IO-Statistics for that query: Scan count 1, logical reads 3605980, physical reads 54288, read-ahead reads 3515407, lob logical readse 0, lob physical reads 0, lob read-ahead reads 0.
Can someone give me a hint what could be the problem with this table? In my understandig, a table scan shouldn't take so much time for only the first 10 rows... Why seems the sql server to load everything? Could it be that the rows have to be sorted before the top 10 can be selected?
Select count(*) from dbo.test needs exactly the same time...
The Query Select top 20000 * from dbo.test runs nearly 6 minutes with this IO Statistics:
Scan count1, logical reads 3606979, physical reads 39229, read-ahead reads 2297229, lob logical readse 0, lob physical reads 0, lob read-ahead reads 0....
My first idea would be to create a clustered index to improve other queries on this table... But would this even improve the performance on the just mentioned queries?
Thanks in advance for any hint or explanation
August 18, 2014 at 3:06 am
No indexes, so a heap?
You've probably run into the scenario where pages aren't deallocated after deletes. Create a good clustered index, see if that fixes the problem
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 21, 2014 at 3:45 am
Thank you very much for your answer!
This was right the way i needed to think!
I spent over the last days a few hours by searching for fragmented Heaps like you suggested and it was quite right.
Instead of creating a clustered index i've decieded to try an other approach and executed: Alter Table dbo.test REBUILD whicht did the job just like a clustered index would have!
Now the table only consumes about 100 MB and all queries run fast again.
To avoid this situation i've read that the table hint "with TABLOCK" takes care that the pages are deallocated after the delete-statement...?
Would this be true if i decide to not use a clustered index?
August 21, 2014 at 4:16 am
mlueke (8/21/2014)
Instead of creating a clustered index i've decieded to try an other approach and executed:Alter Table dbo.test REBUILDwhicht did the job just like a clustered index would have!Now the table only consumes about 100 MB and all queries run fast again.
Still hasn't fixed the root cause. The table is a heap, this will happen again.
Why is it a heap? There are very few good reasons to not have a clustered index on a table (most have to do with bulk loads)
To avoid this situation i've read that the table hint "with TABLOCK" takes care that the pages are deallocated after the delete-statement...?
Sure, if you want every single delete to lock the entire table exclusively. It'll do *wonders* for the concurrency and performance of other queries against that table.
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 21, 2014 at 5:04 am
This table is deleted and refilled by a job which runs at night, so there are no other queries which will be blocked during this Table hint.
But sure you both are right, the clustered index probably will be the best solution and I've justed created one on that table!
Problem solved, thank you guys very much!
August 21, 2014 at 5:27 am
mlueke (8/21/2014)
This table is deleted and refilled by a job which runs at night, so there are no other queries which will be blocked during this Table hint.
Then truncate it rather than deleting. Faster, less log and can't leave allocated pages
But sure you both are right,
Err... both? 😉 :hehe:
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 21, 2014 at 5:43 am
Oops, sry I was kind of confused, i haven't seen that you wrote both answers :Whistling:
Yeah you are right again, truncate seems to be exactly the solution which will be appropriate for this!
Thank you GilaMonster 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply