May 25, 2019 at 4:14 am
Which indices will show cpu bottleneck?memory bottleneck and IO bottleneck?
for example: which indices higher some value, which shows SQL Server has CPU bottleneck or has SQL Server has Memory bottleneck or has IO bottleneck?
how to monitor the values of the indices? thanks!
May 26, 2019 at 5:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 28, 2019 at 11:30 am
The single best way to do this is through waits. What your server is waiting on is where your bottlenecks lie. Here is an excellent article showing how to use wait statistics to figure out what your pain points are. In case you don't know, Paul Randal and his organization are considered to be among the very best at this type of thing, so this information, and all the rest of their site, is considered the gold standard.
This is for diagnosing a system.
If you're looking at queries, it's a completely different ball game. Since we're in the SQL Server 2014 forum, I can recommend you look at Extended Events for gathering query performance behavior. If we were on 2016 or better, I would also add Query Store.
If you're really looking to get into this stuff, then I also recommend my book, link below. It's pretty thorough in and around queries and performance. It's fairly light on systems though. Hardware changes so fast, it's really hard to make recommendations around that. Rely on your wait statistics to understand your hardware.
"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
May 29, 2019 at 7:00 am
Dear Grant Fritchey, thank you for your kind and patient assistance,
I know you are an excellent expert in database. Thank you for your valuable advice!
Sorry to bother you again, can you help me with the following question ? thanks!
how to calculate the RAM used while running a query, for example, I run the following code, how can I know the RAM used by this statement. thanks in advance!
select * from table1 t1 left join table2 t2
ON t1.f1=t2.f1 and t1.f2=t2.f2
where t1.f5='active'
May 29, 2019 at 12:20 pm
Thanks for the kind words. Really not doing that much, but thank you.
The easiest way to answer this question is to use Extended Events. The specific event you'd be looking for in this case is sql_batch_completed. This assumes one query, in a batch. If you have multiple queries in the batch, you need to look at sql_statement_completed. Either way, these events show exactly the number of logical pages processed by the query. That's your number for memory, accumulated 8k pages. I already gave you a link to the introduction to Extended Events. For more, look at my blog or youtube channel. I've posted a lot of hints & tricks & stuff.
Another way to do this, easier, but actually more costly in terms of the effects on your system, is to turn STATISTICS IO on. This will show every object accessed and the logical pages processed. That will tell you the amount of memory used. I used to primarily use STATISTICS IO, but after finding several instances where it affected all my other measurements, I only use it in targeted circumstances. It's easy and quick, but also a little painful for the systems involved. You'll find that majority of people do just use this because it is so simple while setting up and accessing the information from Extended Events is more involved. In addition to simplicity, the one HUGE advantage that STATS IO has over ExEvents is that it breaks down the IO by object. There are ways to also capture that in ExEvents, but it's quite involved, and frankly, a pain the bottom. I'd only do that if forced to by circumstances (excessive load on the system, the need to automate capture rather than do manual capture, stuff like that).
"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
May 29, 2019 at 1:55 pm
if I set "set STATISTICS IO on" , and execute one SQL Query statement it shows as below, can you say it took 1767*8kb memory when runing this state ? if yes, do it mean not including the physical reads memory (below example, physical reads is 4) ? thank you!
Table 'tb_saleOrder'. Scan count 1, logical reads 1767, physical reads 4, read-ahead reads 1763, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
May 29, 2019 at 2:11 pm
Nope. Just look at the logical reads. That's the amount of memory being used by the query.
"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
May 29, 2019 at 2:55 pm
+ size of the execution plan itself
May 30, 2019 at 11:01 pm
if I set "set STATISTICS IO on" , and execute one SQL Query statement it shows as below, can you say it took 1767*8kb memory when runing this state ? if yes, do it mean not including the physical reads memory (below example, physical reads is 4) ? thank you! Table 'tb_saleOrder'. Scan count 1, logical reads 1767, physical reads 4, read-ahead reads 1763, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
To add to what grant said about only worrying about the logical reads for this problem... logical reads (and, all the read categories, really) are pages. You already know that. If you want the amount of memory (or whatever) in Mega Bytes, just divide the number of pages by 128 or 128.0 depending on the level of accuracy you want on the return.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply