Tricks to Identify Which Need Optimization In SQL Server

By: Jimit Shah | Views: 1734 | Date: 17-Jan-2011

some helpful stuff for identifying which need optimization in sql server


Query To Identify The Missing Indexes In T-SQL of SQL Server :

BEGIN

-- Do not lock anything, and do not get held up by any locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 20

st.text AS [SQL]

, cp.cacheobjtype

, cp.objtype

, DB_NAME(st.dbid)AS [DatabaseName]

, cp.usecounts AS [Plan usage]

, qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%<MissingIndexes>%'

ORDER BY cp.usecounts DESC

END


To Find Top 20 Expensive Queries in SQL Server :


SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC


To Find Who Did What When in SQL Server? :--


select sys.dm_exec_sessions.session_id,sys.dm_exec_sessions.host_name,sys.dm_exec_sessions.program_name,
sys.dm_exec_sessions.client_interface_name,sys.dm_exec_sessions.login_name,
sys.dm_exec_sessions.nt_domain,sys.dm_exec_sessions.nt_user_name,
sys.dm_exec_connections.client_net_address,
sys.dm_exec_connections.local_net_address,sys.dm_exec_connections.connection_id,sys.dm_exec_connections.parent_connection_id,
sys.dm_exec_connections.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions inner join sys.dm_exec_connections
on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id


Previous Page Next Page

People Searching On This Page:
  • sys.dm_ optimization
  • email name.net.ms loc:IN
  • email aserver.com.br loc:IN
  • email ms.tco.net.br loc:IN
  • mssql, select * from sys.dm_exec_sql_text
  • dm_exec_query_stats nt_user_name
  • select * from sys.dm_exec_sql_text( )
  • EXIST index MSSQL tip
  • select * from sys.dm_exec_sql_text
  • qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads,
  • sql 2000 dmv mapping
  • dm_exec_sql_text.DB_NAME()
  • mssql underline
  • ms sql server sys.dm_exec_sessions objectID

Related Pages


SSRS Interview Questions - SQL Server Reporting Services

SSRS Interview Questions - SQL Server Reporting Services

Articles | Programming | MS SQL
Date:
20-Aug-2011  Views: 1784

Here we have provided some interview question regarding SQL Server Reporting Services (SSRS) Technology. All the Best. ...
English Speaking Tips And Tricks

English Speaking Tips And Tricks

Articles | Education
Date:
17-Aug-2011  Views: 2161

Do you know now both OMG - oh my god and lolz - which means is kidding or related to that are now registered in the oxford dictionary. Oxford means to ...
Overview of SQL Server Database Triggers

Overview of SQL Server Database Triggers

Articles | Programming | MS SQL
Date:
10-Aug-2011  Views: 1305

A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. ...
High Security Credit Cards: What You Need to Know

High Security Credit Cards: What You Need to Know

Articles | Finance | Credit
Date:
08-Aug-2011  Views: 1136

The 1.7 billion credit cards we use in the US have magnetic stripe technology, which is relatively easy to clone, allowing thieves to commit credit ca ...
DTS vs SSIS - Data Transformation Services vs SQL Server Integration Services

DTS vs SSIS - Data Transformation Services vs SQL Server Integration Services

Articles | Programming | MS SQL
Date:
19-Jul-2011  Views: 2158

Here we have provided some differences between DTS and SSIS. It may be useful to answer interview question related to SSIS (MSBI) ...
Post Your Comments (No Login Require)
Name : (required)
Email : (required)
Website :

Comment : (required)

24  + 9 =     
Comments
People Searched About:
Sys.Dm_ Optimization   |   Email Name.Net.Ms Loc:IN   |   Email Aserver.Com.Br Loc:IN   |   Email Ms.Tco.Net.Br Loc:IN   |   Mssql, Select * From Sys.Dm_Exec_Sql_Text   |   Dm_Exec_Query_Stats Nt_User_Name   |   Select * From Sys.Dm_Exec_Sql_Text( )   |   EXIST Index MSSQL Tip   |   Select * From Sys.Dm_Exec_Sql_Text   |   Qs.Execution_Count, Qs.Total_Logical_Reads, Qs.Last_Logical_Reads, Qs.Min_Logical_Reads, Qs.Max_Logical_Reads,   |   Sql 2000 Dmv Mapping   |   Dm_Exec_Sql_Text.DB_NAME()   |   Mssql Underline   |   Ms Sql Server Sys.Dm_Exec_Sessions Objectid   |  
Google : 739 times | Yahoo : 106 times | Bing : 325 times |