Identifying slow running queries

This post is for SQL Server Database Administrators who have find difficulty in some situations when identifying slow running queries. The DBAs who work in production environment specially might have faced situations where users or other production monitoring teams complaining that suddenly they see a slowness in their systems. The “slowness in the system” a very generic term. This could be due to various reasons. Such as;

• Network issue
• Application server issue
• Database issue

As a DBA, it is a responsibility to make sure everything is clear in database perspective. Before start troubleshooting the broader phenomena, it is necessary to ask few questions to shed some light into the dark area. Below questions might be useful;

How to get database sizes

There are several ways to get database sizes in a server. Following three system tables has the information to get database sizes.

sys.sysfiles

sys.database_files

sys.dm_db_file_space_usage

You also can use following system stored procedure.

exec sp_spaceused

However the easiest way is to use SP_HELPDB system stored procedure. Below script used that SP to get the database sizes in a server.

create table #spdbdesc

(
    dbname sysname,
    dbsize        nvarchar(13) null,
    owner sysname null,
    dbid    smallint primary key,
    created nvarchar(11),
    dbdesc    nvarchar(600)    null,
    cmptlevel    tinyint
)


INSERT INTO #spdbdesc
exec SP_HELPDB

SELECT *
FROM #spdbdesc

Test data generator using T-SQL

Some times DBAs want to generate test data to test some applications designed and developed by themselves. I have faced many situations like this in past. Recently also I had to generate some test data to test one of my research projects. Below is the ERD of the application. It is just four tables.

1. Customer ( master data)
2. Item (master data)
3. SalesOrder (Sales order header data)
4. SalesOrderDetail (Sales order detail data)

How to get the execution plan of a stored procedure?

Think about a situation where you have identified a badly performing stored procedure in Production environment and now you want to see the execution plan of it for further analysis. Please note, executing the procedure with sample parameter values in Production environment should be done with extra care because if the procedure is only SELECT then no issue but if it has DML operations, then issue.

You can use following DMVs to get the execution plan either XML format or graphical one.

USE AdventureWorks2008R2

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID=60

Note: I executed the SP first so that plan will be created. In actual environment this is not necessary.

select Plan_handle from sys.dm_exec_procedure_stats ps

where ps.database_id=DB_ID('AdventureWorks2008R2')

              and object_id=object_id('dbo.uspGetEmployeeManagers')  

--Copy and paste the plan handle to the below query

select * from sys.dm_exec_query_plan(0x05000500EAE8807F40E13287000000000000000000000000)

Cheers for reading this blog post.

How to identify objects used in a stored procedure

Recently there was a requirement from a project team to identify all objects belong to a stored procedure. Actually they wanted only the tables used in a stored procedure.You can use sp_depends with the SP and then you will be able to get all the dependent objects. But the actual requirement was much more complex than that because they wanted to analyze a trace they created for all the SPs captured in the trace.

Sp_depends and sys.sysdepends are provided in SQL Server 2008 for backward compatibility purpose only. So not recommending to use in new developments.

Refer below links for more information.


sp_depends


sys.sysdepends

Rename a constraint

If you need to rename a constraint after creating with typo or due to any other reason, you could follow the below steps.

--creating test table with PK constraint
CREATE TABLE dbo.RenameConstraintTest
(
ID int CONSTRAINT PKC_RenameConstraintTest PRIMARY KEY CLUSTERED
)
GO

--check the existance of the constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO

--rename the constraint
EXEC sp_rename N'[dbo].[RenameConstraintTest].[PKC_RenameConstraintTest]', N'PKC_RenameConstraintTest2', N'INDEX'
GO

--check the old constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO
--check the renamed constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest2'
GO

How to create a procedure in all databases in a server?

I had a situation where I wanted to create a stored procedure in all databases for a specified SQL server.

I created a below code for that purpose.

View Jobs Schedule Information

The code portion given below is useful when you need to get the schedule information of SQL Server jobs. There are several system tables exist in msdb database to retrieve jobs related information. But in this scenario I wanted to see the jobs schedule description which displays in jobs properties UI.

The description is not stored in any system tables as is rather it will do a manipulation by using other values. 


It will use the system SP, sp_get_schedule_description   to prepare description. 



create table #temp_jobschedule
(
job_id uniqueidentifier
,schedule_id int
,schedule_name sysname
,[enabled] int
,freq_type int
,freq_interval int
,freq_subday_type int
,freq_subday_interval int
,freq_relative_interval int
,freq_recurrence_factor int
,active_start_date int
,active_end_date int
,active_start_time int
,active_end_time int
,date_created datetime
,schedule_description nvarchar(4000)
,next_run_date int
,next_run_time int
,schedule_uid uniqueidentifier
,job_count int

)




declare @Job_Id uniqueidentifier
declare @Last_Job_Id uniqueidentifier


select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id


select top 1 @Last_Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id desc


while @Job_Id is not null
begin


insert into #temp_jobschedule (schedule_id,schedule_name,[enabled],freq_type,freq_interval,freq_subday_type,freq_subday_interval,freq_relative_interval,freq_recurrence_factor
,active_start_date,active_end_date,active_start_time,active_end_time,date_created,schedule_description,next_run_date,next_run_time,schedule_uid,job_count)
exec sp_executesql N'exec msdb..sp_help_jobschedule @job_id=@JOB_ID,@include_description=1',N'@JOB_ID uniqueidentifier', @JOB_ID=@Job_Id

select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.[enabled]=1
and j.category_id not in (10,15,13)
and J.job_id>@Job_Id
order by J.job_id

if @Job_Id=@Last_Job_Id
set @Job_Id=null


end


update #temp_jobschedule set job_id=jsch.job_id
from msdb.dbo.sysjobschedules jsch
where #temp_jobschedule.schedule_id=jsch.schedule_id


select j.name,schedule_description,jsrv.last_run_duration
from #temp_jobschedule jsch
inner join msdb.dbo.sysjobs j
on jsch.job_id=j.job_id
inner join msdb.dbo.sysjobservers jsrv
on jsch.job_id=jsrv.job_id

Tables with no Clustered Index

Most of the time it is a good practice to have a Clustered Index on each table. However I've seen numerous instances where there are tables without Clustered Index. The following code will help 
you to identify the list of tables which do not have Clustered Index.