Archive

Posts Tagged ‘sql query’

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

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
 
 
.

Renaming MS SQL Server database

February 8, 2010 2 comments

Renaming a database is not done very often. However, I have done it myself many times.  this can be done in two ways. The common method used for rename is by using ” Alter Database ” script. An example is as shown below.

ALTER DATABASE OldDbName MODIFY NAME = NewDbName

The above script will rename the database “OldDbName” with new name “NewDbName”. Another way of acheiving the same is by using the stored Procedure “SP_RENAMEDB” The syntax is as below

EXEC SP_RENAMEDB 'OldDbName' , 'NewDbName'

“Alter” is preferred over the SP_RenameDB as the SP will be deprecated in the future version of  SQL Server.

SQL Server might throw an error while renaming a database as below.

This is due to the existing conections to the database. to overcome this error,

1.you need to drop all the connections to the database.  Refer to my previous blog for more info on this.https://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/

2. You can set the database to single user mode . refer the link for more info on this. https://awesomesql.wordpress.com/2009/08/04/changing-the-collation-of-a-database/

Script to drop all connections to a Database

February 8, 2010 4 comments

There are instances when we may have to drop all the connections to a database for example to rename a database. The script below can be used to drop all the connections to the database. 

DECLARE @dbname nvarchar(128)

SET @dbname = ‘DB name here’ — db to drop connections DECLARE @processid int SELECT @processid = min(spid)

Read More . . .