Archive

Archive for the ‘Functions and DMVs’ Category

Transact SQL code to identify queries with highest I/O in SQL server

June 22, 2018 Leave a comment

Use the below transact SQL code to identify the queries / procs consuming most I/O. You can combine this with the execution plan of the query to derive insights on the changes required to improve the query performance

 

SELECT TOP 100
TOTAL_LOGICAL_READS
, TOTAL_LOGICAL_WRITES
, S.EXECUTION_COUNT
,( TOTAL_LOGICAL_WRITES + TOTAL_LOGICAL_READS ) AS TOTAL_IO
,( TOTAL_LOGICAL_WRITES + TOTAL_LOGICAL_READS ) / S.EXECUTION_COUNT AS AVERAGE_IO
,T.TEXT AS QUERY_TEXT
,OBJECT_NAME(T.OBJECTID)
,DB_NAME(T.DBID) AS DATABASE_NAME
FROM SYS.DM_EXEC_QUERY_STATS S
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE) AS T
WHERE T.DBID = DB_ID()
ORDER BY TOTAL_IO DESC, AVERAGE_IO DESC;

 

Above code in combination with Transact SQL code to identify longest running queries in SQL server can be used as first steps to identify the issues in your query performance.

 

Advertisements

Transact SQL code to identify longest running queries in SQL server

June 22, 2018 1 comment

Provided below is a short and quick script to get the longest running queries and the query plan from SQL server for any specific database. Remove the Where clause in the code, if you want to check for all Databases. you can check execution plans of  queries to see what inefficiencies are causing the queries to run longer and possible improvement to queries like indexes or  rewrite a query differently.

 

USE MyDB;

GO;

SELECT TOP 100
S.PLAN_HANDLE
,A.SQL_HANDLE
,T.TEXT AS QUERY_TEXT
,S.EXECUTION_COUNT
,S.MAX_ELAPSED_TIME
,COALESCE(S.TOTAL_ELAPSED_TIME / S.EXECUTION_COUNT, 0) AS AVERAGE_ELAPSED_TIME
,S.CREATION_TIME
,S.LAST_EXECUTION_TIME
,P.QUERY_PLAN
FROM SYS.DM_EXEC_QUERY_STATS S
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(S.PLAN_HANDLE) AS P
CROSS APPLY SYS.DM_EXEC_SQL_TEXT( S.SQL_HANDLE ) T
WHERE T.DBID = DB_ID()
ORDER BY
MAX_ELAPSED_TIME DESC

The string function SOUNDEX() and its purpose in SQL.

April 25, 2011 Leave a comment

The Soundex code was developed to help negate the effects of all the spelling variations that can occur for similar sounding names ( Smith, Smithe, Smythe, etc.).  This way, users can index records based on a Soundex code based on the sound  and “not” by how it is spelled.

The Soundex system is not infallible – the surnames Gough and Goff sound the same (Goff), but a different code is formed for these two names.  Soundex can however be used to a great extent to get a desired result ( atleast closer to a desired result) This is a commonly used algorithm by many researchers.

In SQL a string function is available for this and is usually comes handy.  The function is

Continue Reading …

.

Executing the script in batch file using “sqlcmd” command

April 25, 2011 1 comment

 often create batch files to execute sql scripts ( specially SPs) and then schedule it using SQL agent job/Task Scheduler. In this post , I will show you how to create a batch file with sql commands.

First of all, The command that is used to execute SQL queries from commnd prompt is “SQLCMD“. The general syntax of this command is available in the link. I will go with a simple one as below

sqlcmd –E –S Servername –Q Query -o “Output Path”

here
-E = Trusted connection (windows authentication)
-S = Server Name
-Q = CommandLineQuery
-o = Output file path
a simple command on the commnad prompt can be written as
 
 
.

Reindex the database with high fragmentation using “DBCC DBREINDEX”

October 8, 2010 Leave a comment

DBCC is one of the most commonly used commands by DBAs across the globe. Today I will introduce a simple way to reindex a specific or all indexes in a table using DBCC.

The command used for this is

DBCC DBREINDEX

General Sytax

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

The best feature of this command is that,

Continue Reading …

 

 

Set Nocount On – Hide the number of records affected by a query

October 7, 2010 Leave a comment

Often it might be necessary to hide the number of records affected by a query. It can be a simple select or insert  statement . Ususally, as soon as we execute a query a message pops up on the messages tab as shown below with the number of records affected.

Continue Reading ….

Script to List Linked Servers

October 7, 2010 Leave a comment

In a large server , it is very common to create high number of linked server connections. it might be required ( not very often) to use this list in the SPs or other objetcs created. Below is a simple query / script that can provide the list of linked servers used.

Continue Reading ..