CPU?Memory and IO bottleneck check

  • 892717952

    SSC Veteran

    Points: 219

    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!

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Grant Fritchey

    SSC Guru

    Points: 395264

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 892717952

    SSC Veteran

    Points: 219

    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'

  • Grant Fritchey

    SSC Guru

    Points: 395264

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 892717952

    SSC Veteran

    Points: 219

    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.

  • Grant Fritchey

    SSC Guru

    Points: 395264

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Andrey

    Old Hand

    Points: 383

    + size of the execution plan itself

  • Jeff Moden

    SSC Guru

    Points: 993786

    892717952 wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply