Monday, April 3, 2017

SSMS Custom Query shortcuts Update.

Haven't touched that area for a long while.
Here is the link to my original post with all descriptions and implementation process:

In this post I'll only list changes made since a year ago:

The newest version of SSMS Query Shortcuts is available HERE.

Ctrl+4 Changes/Improvements

- Fixed an issue when database collation is different from a server;

Ctrl+5 Changes/Improvements

- Added statistical performance dataset for all mounted drives used by SQL Server;
- Added column: "# of VLFs" for Log files;
- Added column: "% of CPU usage" by database;
- Added column: Database "Access state";
- Added column: Database "Mode";

Ctrl+5: Drives' stats + couple of new columns:

Ctrl+6 Changes/Improvements

- Fixed memory and "Buffer cache hit ratio, %" metrics;
- Added essential SQL Server counters' changes for a time frame capturing (Wx-option);
- Added I/O SQL Server counters' changes for a time frame capturing (Wx-option);
- CPU diagram is now reversed and in only two colors;
- Added currently set Trace Flags list data set into informational option  (I-option) ;
- Added list of SQL related Services into informational option  (I-option) ;

When you type would say "W10" (wait for 10 seconds) then select it by a cursor and press Ctrl+6 the script will capture Wait statistics, essential SQL metrics and I/O metrics. Then script waits for 10 seconds and compares previous values with the new ones and reports any difference.
That option can bring a lot of information telling you what SQL Server is doing right now and what potential bottleneck can be.

That is CPU usage diagram you get after simple "Ctrl+6". After it changed to reverse order it is easier to define timing. For instance on the diagram below you can see that SQL Server experienced very heavy CPU usage about 260-270 minutes ago.

Ctrl+8 Changes/Improvements

- Fixed diagram's scale by implementing logarithmic measuring;
- Top 10 diagram includes "Distinct Top 10" combination of MAX CPU, MAX I/O, & MAX # of executions. Number of top queries can potentially rise up to 30;

Now Query executions' diagram can have more than 10 queries and because it is in logarithmic scale now there are no hassle with circle sizing.
Axis-X represents amount of CPU used by a query
Axis-Y represents amount of I/O used by a query
Circle radius represents number of query executions.
If you move cursor to a particular circle you'll see Queries metrics
To see more details on the query you want to research, such as query text, execution plan, etc.
You have to copy-paste "query_hash" value to an editor window, select it and press Ctrl+8 again.

No comments:

Post a Comment