February 8, 2008 at 1:16 am
Hi .
I have three questions that I just need some clarity on even if you guys dwell deep into the sql architecture it is fine .
1: In Profiler you get the reads datacolumn and books online state "Number of logical disk reads performed by the server on behalf of the event" .
These reads are from cache because they are specified as logical ? ..how can I determine if the pages are not in the bpool that sql will do a physical read from disk into cache ? . So if I have a query that the reads value is 2000 is that the query is doing 2000 logical reads from cache ? .
2:
The total CPU time and the total CPU time of SQL server.exe .
If the total CPU time of the server is say 50 % and SQL server.exe is 80 % ...is SQL server using 80 % from the 50 % total CPU usage ?
We have a 2 X 16 CPU nodes in our cluster and sometimes the total CPU of the node is 50 % but SQLserver.exe is something like 170% ..can someone explain this to me ?
Is this the total CPU usage of this specific instance on all physical CPU's ...must I devide the 170% by the total amount of physical CPU's ? ..
3:
How to I determine the total cost of a query in sql server profiler ?
speedy response will be appreciated
February 8, 2008 at 5:39 am
1. Logical reads are all reads, physical from the disc as well as from memory. To determine physical reads as seperate from cache reads, you need to get performance monitor counters and synchronize them with your trace.
2. You nailed it. CPU time total is what the box itself is doing. SQL Server CPU time is the percentage of time that SQL Server is operating within that total CPU time. I hadn't noticed a greater than 100% count on SQL Server before, but it must be explained by the number of CPU's. Maybe I need to pay better attention to this number. Someone else may have more to add.
3. Honestly, it depends. I take the output from Profiler into a table and then run reports against to generate three lists. First, the top 10 procedures by the number of calls. Second, the top 10 procedures by total duration. Lastly, I had a value I called cost, which was average duration times the average number of calls times the average cpu. It was a somewhat meaningless number. But you'd almost always find two or three procedures on all three lists. I'd go after those procedures first. A more logical approach is advocated by Itzik Ben Gan. Instead of looking at duration or calls, look at wait times to determine which procedures to attack. There's also a white paper from Micrsoft on the same topic.
Hopefully that's a little helpful. Someone else will probably have more details on 2 and I'm sure there are other opinions on 3.
"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
February 8, 2008 at 5:51 am
Thanks for the feedback
The CPU one is kinda tricky at this stage ...I think this might be because of hyperthreaded cpu's ..I hate them .
W
February 12, 2008 at 8:44 am
Re 1:
All reads are from the buffer cache, aka logical reads. If the data pages are not in the buffer cache, physical reads are first performed to bring the data into the buffer cache. So in your example, if you see a value of 2000 for reads in profiler, there are various possibilities on the number of physical reads that were performed:
- if this was the first time that the data was retrieved from the table, there would have been ~2000 physical reads performed
- if part of the data was already in the buffer cache, maybe because another query requested similar data, the physical reads would be less than 2000
- if all the data was already in the buffer cache, maybe because another user ran the exact same query moments ago, no physical reads would be performed
>> ..how can I determine if the pages are not in the bpool that sql will do a physical read from disk into cache ?
You could run SET STATISTICS IO ON, then run your query. You should then see the number of physical reads that SQL Server had to perform, at that point in time.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 19, 2008 at 10:12 am
CoetzeeW (2/8/2008)
3:
How to I determine the total cost of a query in sql server profiler ?
By no means the only way, but I tend to focus on the reads column, specifically when I'm looking for poorly-indexed queries. The more the reads the more you may have a problem in terms of inadequate indexing.
Insert your profiler results in a table and sort by reads column, that should give you a good starting point.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply