SQL Server – Page 11 – LincolnBlog.net

Getting the current connections to a SQL Server

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT * 
FROM sys.dm_exec_connections

1 2	SELECT * FROM sys.dm_exec_connections

Views – 1974

Keeping Indexes online while reindexing

Posted on 01/10/2014 by Lincoln Bovee'

This will also display the SQL Statement that is the offending blocker. Online index operations: Online index operations were added as an availability…

Top 10 SQL Administrators Scripts You Should Know (link)

Posted on 01/10/201401/21/2019 by Lincoln Bovee'

Top 10 SQL Scripts You Should Know Views – 1793

Adding SELECT Excel hyperlinks to recordsets

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

Users frequently want spreadsheets with embedded links to web pages, one per row. This is one way to get them.

SELECT '=HYPERLINK("http://website/somepage.aspx?id=' + CAST(Myid AS VARCHAR) + '", "' + CAST(Myid  AS VARCHAR) + '")' AS linkToItem
FROM MyTable

1 2	SELECT '=HYPERLINK("http://website/somepage.aspx?id=' + CAST(Myid AS VARCHAR) + '", "' + CAST(Myid AS VARCHAR) + '")' AS linkToItem FROM MyTable

Views –…

Using SQL Profiler to trace a sproc

Posted on 01/10/2014 by Lincoln Bovee'

Using SQL Profiler to trace a Stored Procedure call Views – 1799

Getting the plan handle for a sproc

Posted on 01/10/201403/06/2014 by Lincoln Bovee'

You can use this T-SQL to get the plan handle for a sproc that has a bad execution plan:

SELECT 
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    cp.cacheobjtype AS ObjectType,
    cp.plan_handle AS PlanHandle,
    deps.cached_time as CachedDate,
    deps.last_execution_time AS LastExecuted,
    deps.total_worker_time AS TotalWorkerTime,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan,
    GETDATE() AS TimeQueryExecuted
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    INNER JOIN sys.dm_exec_procedure_stats deps ON deps.plan_handle = cp.plan_handle
WHERE 
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'usp_SomeSproceName';

SELECT

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

cp.cacheobjtype AS ObjectType,

cp.plan_handle AS PlanHandle,

deps.cached_time as CachedDate,

deps.last_execution_time AS LastExecuted,

deps.total_worker_time AS TotalWorkerTime,

st.TEXT AS QueryText,

qp.query_plan AS QueryPlan,

GETDATE() AS TimeQueryExecuted

FROM

sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

INNER JOIN sys.dm_exec_procedure_stats deps ON deps.plan_handle = cp.plan_handle

WHERE

cp.objtype = 'Proc'

AND OBJECT_NAME(st.objectid,st.dbid) = 'usp_SomeSproceName';

Make sure, to…

Viewing the execution plan for a specific Sproc

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT 
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'YourStoredProcedure';

SELECT

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

st.TEXT AS QueryText,

qp.query_plan AS QueryPlan

FROM

sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE

cp.objtype = 'Proc'

AND OBJECT_NAME(st.objectid,st.dbid) = 'YourStoredProcedure';

Views – 2158

Trouble-shooting T-SQL which runs fast but the Sproc is slow (link)

Posted on 01/10/201401/10/2014 by Lincoln Bovee'

Setting ANSI_NULLS ON will make make the Sprocs run fast. See this article: Query is fast but Sproc is slow Views – 1754

Troubleshooting Query Slowness in SQL Server (link)

Posted on 01/10/201401/10/2014 by Lincoln Bovee'

Here is a great article on trouble-shooting T-SQL query slowness: Trouble-shooting SQL Server Slowness Views – 1758

Using UPDATE STATISTICS

Posted on 01/10/2014 by Lincoln Bovee'

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the…

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, Linux and PostgreSQL etc…

Category: SQL Server

Getting the current connections to a SQL Server

Keeping Indexes online while reindexing

Top 10 SQL Administrators Scripts You Should Know (link)

Adding SELECT Excel hyperlinks to recordsets

Using SQL Profiler to trace a sproc

Getting the plan handle for a sproc

Viewing the execution plan for a specific Sproc

Trouble-shooting T-SQL which runs fast but the Sproc is slow (link)

Troubleshooting Query Slowness in SQL Server (link)

Using UPDATE STATISTICS