SQL Server - Current Execution Statistics

Happy holidays! Most of the times we have heard that the queries are not working as expected. Such simple statements just puts the investigation back to the DBAs or the SQL developers. The most common problem is what exactly is happening in SQL Server. Thanks to DMVs that this question can easily be answered without having admin permissions. What's needed is - VIEW SERVER STATE permission.

Query in below link file shows the snapshot of SQL Server at that particular time including CPU Time, Memory blocks, I/O issues, exact query being executed, query plan being used and much more. This have helped me a lot in the past and hope it helps you guys as well. Shoot me the questions which you think are not answered using this query and I'll try to answer them with best efforts.

https://drive.google.com/file/d/0B5ukOz8LhAaNNnQwRnZJazNjTlE/view?usp=sharing

SSAS Tabular Model - Drillthrough & Other Actions

BI dashboards, reports etc. throw some random numbers and graphs on the screen but the data analysts always wants to know that how these numbers are being cooked up. SSAS provides actions as one of the method to show that. Although actions are far more powerful than just showing drillthrough. They can add lots and lots of context to the data being shown.

With tabular model actions were sort of difficult but with the help of BIDS Helper it can be done with lots of ease. Here is the reference link showing the use.

http://bidshelper.codeplex.com/wikipage?title=Tabular%20Actions%20Editor

Finding Outliers - Simple Statistical Way

Outliers? Why do we need them?

Too big or too small value is outlier in statistics. We need to consider outliers separately for good business decisions and beautiful graphs. Business decisions are mostly taken around (a) most frequent case OR around center of the population and (b) exceptions i.e. outliers and they are far away points from the center. My take is outliers should never be rejected from data but be treated as special cases for further analysis. Outliers may open new doors of possibilities.

The most common way to find an outlier is by using quartiles or percentiles. Quartile 1(Q1) == 25th Percentile,Quartile 2(Q2) == Median == 50th Percentile & Quartile 3(Q3) == 75th Percentile. To find out the accepted data range we need to find the spread of the data and most of the times it is done using either by Standard Variation or by IQR(Interquartile Range) i.e. (Q3-Q1) in simple way. Below figure gives the simple formula to find the outlier.

In simplest way any data point which is out of the (Q1 - 1.5 * IQR) and (Q3 + 1.5 * IQR) they can be treated for outlier analysis. There are many other methods but this one makes most sense based on common business analysis. This 1.5 is statistically negotiated value i.e. constant.

Here are a couple of examples from web search :)

http://www.itl.nist.gov/div898/handbook/prc/section1/prc16.htm

http://www.mathsisfun.com/data/outliers.html

Don't disable SQL Jobs like ...

How to disable a SQL Server job?

This can't be a good enough question to post a solution. But sometimes it is. In the recent past we figured out that the SQL Server jobs are disabled but they were starting at their scheduled time. Amazing! What a f**k is happening? Why is this happening? Is SQL Server is not reliable enough?

One of the curious operations support guy figured out that the jobs were disabled by updating the metadata using the query "UPDATE [dbo].[sysjobs] SET [enabled] = 0 WHERE name IN (...)". The "Job Activity Monitor" showed that all the jobs are disabled. What's wrong then?

SQL Agent uses caching and caches the job status. Simple UPDATE statement will simply change the metadata and not change the cache. So it is recommended to use the the UI from SSMS or call sp_update_job. This method updates the cache as well.