Lincoln Bovee’ – Page 13

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 – 1743

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 – 1767

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 – 2114

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 – 1722

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 – 1722

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…

Clustered and Nonclustered Indexes Described

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

Clustered Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns…

SQL Server Roles

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

SQL Server-level Roles • SysAdmin: Allows users to perform any activity on the server. • ServerAdmin: Permits users to manage configuration options on…

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, .Net etc…

Author: Lincoln Bovee'