Unfortunate Ending of the Saying … Jack of All Trades

Category Archives: SSMS

I like using Ola Hallengren’s maintenance scripts. Super configuratible, logging, SQl Agent job creation and etc. Sometimes I want to spot check on some of the statistics. Stale statistics can cause cardinality issues with SQL plans.

We learn how database statistics are used:“The query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results. This topic discusses statistics concepts and provides guidelines for using query optimization statistics effectively.”

The sample rate is set to full s or 100 percent of the rows will be scanned. A full scan will create overhead. Need to always look at the impact to your environment. You could have old statistics and not used or duplicate statistics. In some cases, the higher sample rate can actually cause the plan be less optimal.

I found a useful script to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time periods.

Stored Procedure Executions

For example, you have a under performing billing process. Your system fires multiple procedures multiple times. You could use something like this to get an idea of execution counts, worker time, average elapsed time and max logical reads. It will point you in the right direction procedure that can be optimized. Tweak this to fit your needs.Read more »

Many third-party tools have pretty lead blocking graphs and etc. You may not have access to something than grand. My friend and co-work JD Pellerin wrote this script. He used this as a basis for an SSRS report that can be executed by operations teams to identify potential batch issues. Play around with tweak it to fit your needs.

Many people are familiar the Windows Command Prompt. You can open one. type “HELP” and click enter. It will return descriptions of various OS commands. You can type “-?” after a command and get a list on all the switches that can be used with that command. Go ahead and try it (sqlcmd -?)The system is simply returning metadata.SQL has several commands that return metadata about system and user objects. We will look at the OBJECT_DEFINITION() command.

Windows Command Prompt and HELP command.

From the link we learn that it “Returns the Transact-SQL source text of the definition of a specified object.” We all know you can right click on an object in SSMS and script it out. Why use this? Output to SSMS has a limit on how much can be returned at a time. Large object like procedures can be thousands of lines long and will be truncated. AH!

Beside of the nerd cool factor of being able to return source code, it can be handy. One use is to search for the occurrences of specific objects or syntax within objects. Maybe you want to see what objects reference a certain table or maybe find objects that use a specific QUERY HINT.

Like Part 1 and Part 2, I use a similar query to find latency in AlwaysOn Availability Groups. The queries will return one line for each DB in the group. Part 1 , we looked at a simple query to grab the current status of the replica. Part 2, we looked at populating a Global Temp table every minute to gain a historical perspective of our AG latency. This query will look into creating an email notification when certain criteria is met. It will send the current status to a specific set of email recipients.

The example will be around specific criteria set as a step used in a SQL Agent job. If you use something like this, you need to tweak it to you organization’s needs. I do suggest that you set the job to a frequency that will not overflow your inbox with notices.

Send an email from server when latency for a specfic database datbase is over x number of minutes.

Alter If statement > value for specific needs. This example fires of fthe email when latency is over 60 minutes.

Sometimes there is a need to maintain some historical content on how current your Availability Group is performing. The following script builds off the original script in Part 1. It populates a global temp table with information every minute. I really got the idea from Brent Ozar’s sp_SQL Blitz. I took the road of least intrusive by creating the ##LatencyCheck_tbl global temp table. Sergey Gigoyan has a good post on global temp tables and good links at the end of the post, too.

The ##LatencyCheck_tbl temp table is populated after 1 minute and every minute until cancelled. Need to make sue the table gets dropped after you have done you analysis. It is a good idea to keep an eye on the size of the table, too (execute sp_spaceused ‘##LatencyCheck_tbl’). Like any temp table, service restart will drop the table the process cancelled. If this is something you would like always run. you can look into creating a SQL Agent job or even a procedure that auto starts. I don’t suggest doing that. If you do choose to do so, you should make sure you monitor of have a process in place to reduce the size of the temp table. Also, look into grabbing Perfmon counters and Extended Events. Everything has overhead and we do not want to succumb to the “Watcher Effect”..DAH! DAH! [Organ music].

I needed a quick query to get some server information using sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) . It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.

There are tons of thing in this world that I really don’t understand. SQL Server XQuery is just one of those just out of reach. Poking around trying to answer a question for a developer, I found the following XML script examples. Teh writer provides excellent explanations and examples. Immediately found the right path(pun intended) to go down. I so wished my brain worked this way!

One thing I forget to do often is to include the actual item I’m returning in the Select Statement in the Group By. Meaning , if I use a case statement of some function to change the value that I’m returning, then I should include that and not the base column name in the Group By part of the select statement

/*
If you write something like this and the Col2 has multiple 'Unique' Values
then you will get a row for each value distinct Col2 value that says 'Other'
*/SELECT [Col1]
,Case When [Col2] = 10 then 'Ten'
Else 'Other' End as [LameExample]
,Count(*) as CNT
FROM [dbo].[lameTableExample]
GROUP BY [Col1], [Col2]

-- This Example will return Only two rows

SELECT [Col1]
,Case When [Col2] = 10 then 'Ten'
Else 'Other' End as [LameExample]
,Count(*) as CNT
FROM [dbo].[lameTableExample]
GROUP BY [Col1]
, Case When [Col2] = 10 then 'Ten'
Else 'Other' End

Select Case has a simple overview of using CASE in a SQL Query with the SUM() function.

Sql server has some great built-in commands procs that can help you script server or database wide commands. sp_MSForEachTable and Sp_MSForEachDb

When first looking at a database , I sometimes like to run the following:
execute sp_MSforEachTable @command1=‘Print ”?”; Select Count(*) as [? – (count)] from ?;Select Top 10 * from ? ‘

I used the sp_MSforeachDB just the other day to set all the non-system
databases on a test server to Simple Recovery mode.Just made a quick stored procedure that I passed the ? value and filter out the system databases ( Master, Model, Tempdb and MSDB).