February 18, 2014 at 12:02 pm
I have a query (from an MVP - apologizies to the author for not remembering where I got it) that shows what tables are consuming the buffer pages. It shows the table name, index name, index type, buffer page count buffer MB.
My question is, when I see HEAP as the index type, is that telling me the query or queries that has put or is keeping that table in the the buffer pool is not using any of the indexes created on the table?
Thanks much.
February 18, 2014 at 1:30 pm
I guess your query uses buffer_descriptors joining with allocation_units views.
No, it does not mean that it's not using indexes. It may use indexes for index scans or seeks, but to go for row lookup, it needs the table. If it happens to be heap, it uses it.
February 18, 2014 at 1:37 pm
I guess your query uses buffer_descriptors joining with allocation_units views.
Exactly.
OK. Was wondering because the tables I always see, taking the most space, have no clustered indexes. Half-dozen or more non-clustered, but no clustered.
February 18, 2014 at 2:06 pm
To see if you really have table scans on those tables, you can break out Profiler, and see if you can get the number of table scans that happen on those objects. there is a category called Scans. The object ID column is not picked by default, so you will need to pick that.
The really tricky part, of course, will be determining if those scans are bad scans, or just scans.
February 19, 2014 at 6:24 am
Server-side trace, not Profiler, please. The Profiler gui can have serious negative impacts on performance and really shouldn't be used against production systems.
Further, since you're on 2008, you could look to using extended events. They're even lower cost than trace events and can be filtered better. The only issue is there's not cool GUI available to consume the data as there is with trace data. You'd have to write queries against the XML.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 19, 2014 at 12:27 pm
A "heap" is a table without a clustered index. It may or may not have nonclustered indexes.
You should review heaps carefully. Typically it's far better in SQL Server for a table to have a clustered index, partly because then SQL manages freeing/reusing deleted space better.
If there's only one nonclustered index on a heap, a quick-and-dirty method would be to drop the nonclustered index and create a clustered index with the same key(s).
Of course, technically you really should review missing index info and existing index usage first, but if you're in a hurry and/or don't have someone really knowledgeable about indexes around, you might try the q-and-d method first ;-).
Edit: edited for readability.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 19, 2014 at 1:22 pm
Vendor system - I won't change their design. App was recently upgraded and our users had a streak of timeouts in the front end and were complaining. I was poking around and saw no clustered indexes on 4 of the top 5 buffer users. So I looked further and was surprised by the overall lack of clustered indexes. This vendor is not a small outfit!
The timeouts have stopped. Both the front and back ends are new to VM. Personally I think our virtual environment was under duress for a bit.
February 20, 2014 at 12:54 am
Looks like the problem got resolved and you are relieved. However, as a friend posted that we can used trace to capture scans, I think it is a better practice to use DMVs to find out the most expensive queries and then analyze the objects and their definition.
I use trace only mostly when I am hunting for unknowns, but there are still a lot of options which can provide almost same information like ring buffers introduced in SQL 2008 I think which can tell you about a lot of processes, why they failed and which layer they failed. Only thing is if you don't use these options often, it will tend to fade away from memory gradually!! 😀
Cheers!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply