Tag Archives: SQL

Database Statistics Health and Update Scripts

Posted by on January 14, 2016 Comments Off on Database Statistics Health and Update Scripts

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.

Rule of thumb, your queries will perform better with fresh statistics.

I have two scripts to I use to find the health of statistics and update statistics based upon certain criteria.
Read more »

AlwaysOn Availability Groups – Query to Find Latency Part 2

Posted by on December 30, 2015 Comments Off on AlwaysOn Availability Groups – Query to Find Latency Part 2

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].

Read more »

Script to Get SQL Agent Job History

Posted by on December 27, 2015 Comments Off on Script to Get SQL Agent Job History

Wanted to share this script. I pieced it together some time ago from various scripts. I think this might be the source for at least some of the query.

Removing the step_ID from the where clause will show run times by step_id and Total RunTime (step_id 0).
Read more »

Simple Index Usage Query

Posted by on December 27, 2015 Comments Off on Simple Index Usage Query

Yes, another index usage query. I adapted this a tiny bit to add a time element to it. DMVs will flush out any information when the services are restarted. I omitted system usage information to gear it more toward user created objects. Also, added the users statistics together to get a better Idea of total usage. It gives the user a good starting point to see utilization of current indexing. Conversely, Microsoft provides a “Missing indexes” DMV named sys.dm_db_missing_index_details. Don’t have blind faith in the suggestions. They are based on specific activity since last restart.
Read more »

Query to View Active Sessions

Posted by on December 27, 2015 Comments Off on Query to View Active Sessions

I want to start out by saying I am not sure about the origin of this query. I use it frequently to find activity on my servers. I left some of the code commented to show a few example items you can add and additional search criteria. I really encourage anyone to grab a query and alter it to show information useful to them.

It uses sys.dm_exec_query_plan() (via inline select forgive me) to grab the query plan when possible. Additional information to grab performance information and SQL code executing uses these DMVs sys.dm_exec_requests, sys.sysprocesses and CROSS APPLY sys.dm_exec_sql_text(). Use the links to see the information being returned.

Read more »

T-SQL: Cursor Example

Posted by on December 27, 2015 Comments Off on T-SQL: Cursor Example

ARTICLE FROM ORIGINAL SITE – Young and Dumb!

Database Cursors have long been a favorite of mine. It allows you to take a subset of data and output the information in various ways. I use them for such things as building the body of an automated email with a cursor set within a stored proceedure. You can return output to build a cleaner report output, etc.Example

Select 'Case Created: '+convert(Varchar(20), @DATE ) + ' Case Number: '+ convert(varchar(10), @CaseNum)

We will start with the basic frame work and build from that:

We will use a two tables named SomeTable and Other Table
(The tables are actually from MySQL – I was lazy.)
Read more »

T-SQL: Select Case Example

Posted by on December 27, 2015 Comments Off on T-SQL: Select Case Example

ARTICLE FROM ORIGINAL SITE – Young and Dumb!

Select Case allows you to present data in various way. You can take numbers and return text values and express
certian conditions based on the value of a certain column.

Another example would be that you have a table Naughty_Nice in the Santa Database a data column Nice (INT Datatype) .
There are two distinct values in the column. The Value of 0 equals ‘No’ and the value 1 equals yes. You wanted to speed
stuff up and use less space by selecting a number and not use text Naughty/Nice.

Santa needs a list and he complains that 1 and 0 will not make sense to him at 30,000 feet. It’s so hard to
communicate to a guy that still thinks wooden toys are a big hit. He’s a bit too hands-on, and lacks the technical
skills to really understand that we want to optimize the database.
Read more »

Script Commands to Disable/Enable SQL Agent Jobs

Posted by on May 20, 2014 Comments Off on Script Commands to Disable/Enable SQL Agent Jobs

The script below is a quick way to create disable and enable SQL Agent job commands for existing jobs. It will script out the correct ‘EXEC msdb..sp_update_job’ statements. In this example, the where clause filters for “enabled = 1”, but you can use it create statements based of various job characteristics. We tend to use these when migrating to new servers and want to leave the old server in place. 

/*
Script commands to disable/enable jobs
5/20/2014 JCD
Tested: 2005, 2008 R2 and 2012 SP1

You can further filter jobs with other sysjobs columns to specify
an even smaller subset of enabled jobs. Good examples for adding
filers are owner_sid, description and category_id.

*/
SET NOCOUNT ON
GO

USE msdb
GO
-- Script disable commands for enabled jobs
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=0;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
-- Script enable commands to enabled jobs after disabling. Should be run when creating the disable script
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=1;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO

SSRS Active Session Information

Posted by on March 11, 2014 Comments Off on SSRS Active Session Information

SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000.  A really awesome tool for data delivery.  Most SQL Server environments heavily leverage this powerful tool.  Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3).  SSRS also supplies a text log with additional details for errors and warnings.  All are  useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?

Recently, I needed to  identify an active report that was creating a considerable amount of blocking.  We were able to identify a reporting login , verify the  blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.  As I mentioned, for the most part the ReportServer database gives us historical information.  I always based most of my queries on the ReportServer database.  In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!
Read more »

SQL Query to script DTUTIL.exe SSIS Export Commands

Posted by on February 18, 2014 Comments Off on SQL Query to script DTUTIL.exe SSIS Export Commands

There are several reasons why DBAs will need to export/import SSIS packages for upgrades, development environments and backups.  Many use SSMS to do imports and exports of the dtsx packages. It isgreat unless you need to export several or even all the packages. Microsoft supplies a command line tool dtutil.exe to import, export, delete, update and verify SSIS packages.

I wrote the script below to query msdb and script out the dtutil commands. I save the output to text or file. It can be executed windows batch file(.cmd or .bat).  If your environment uses configuration files(.dtsconfig), make sure you grab a copy of those, too.

USE msdb
GO
SET NOCOUNT ON 
GO 
-- Scripts  DTUTIL commands 
-- Results to Text 
-- Script out commands to execute on target server
-- Output to be used as a windows batch file
-- Tested: SQL 2008 R2  and SQL 2012
-- REMOVE  " ---- " before saving as a .cmd or .bat file
-- JCD 01/29/2013

DECLARE @folderlocation VARCHAR(300)

-- Set location of folder to store 
SET @folderlocation = 'S:\SSISPackages' 

--------------------------
--- Do not alter below ---
--------------------------

-- Add Source Servername to folder structure (ex.C:\SSISExport\MyServer\)
SET @folderlocation = @folderlocation + '\'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'\'

--- Create Folder Structure
SELECT  'MKDIR "'+@folderlocation+'"'
UNION
SELECT 'MKDIR "'+ @folderlocation+RTRIM(foldername)+'"'
from [msdb].[dbo].[sysssispackagefolders]

-- Create DTUtil Commands
select 'DTUtil /SQL ' + 
'"\' + CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END +
P.name + '"' +  ' /SourceS "'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'" /Encrypt  FILE;"'+@folderlocation+ 
CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END + P.name + '.dtsx";0 /Q'
from [msdb].[dbo].[sysssispackages] P inner join [msdb].[dbo].[sysssispackagefolders] F 
on P.folderid = F.folderid
ORDER BY F.foldername

GO