Friday, November 12, 2010
Thursday, November 04, 2010
Thursday, October 28, 2010
Getting a SQL Server RowCount Without doing a Table Scan
SELECT [name]
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO
----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO
CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO
----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO
CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
Friday, October 22, 2010
SQL server CPU Allocation and usage
--general sql configuration
EXEC sys.sp_configure
--server available CPU
select cpu_count from sys.dm_os_sys_info
--server used cpu by sql
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
--server physical memory
select physical_memory_in_bytes from sys.dm_os_sys_info
EXEC sys.sp_configure
--server available CPU
select cpu_count from sys.dm_os_sys_info
--server used cpu by sql
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
--server physical memory
select physical_memory_in_bytes from sys.dm_os_sys_info
Friday, October 15, 2010
Friday, October 01, 2010
Custom Reports Library - SQL Server
SQL Server Manageability Team Blog
http://blogs.msdn.com/b/sqlrem/
http://blogs.msdn.com/b/sqlrem/archive/2010/06/06/mdw-overview-report-for-data-collector-mdw-report-series-part-1.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/19/mdw-overview-report-for-data-collector-server-activity-mdw-report-series-part-2.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/21/server-activity-history-report-mdw-report-series-part-3.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx
http://blogs.msdn.com/b/sqlrem/
http://blogs.msdn.com/b/sqlrem/archive/2010/06/06/mdw-overview-report-for-data-collector-mdw-report-series-part-1.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/19/mdw-overview-report-for-data-collector-server-activity-mdw-report-series-part-2.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/21/server-activity-history-report-mdw-report-series-part-3.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx
Thursday, September 30, 2010
sp_who2 - in detail analysis with temp table
drop table #tmp
create table #tmp
(SPID int,
Status nvarchar(100),
Login nvarchar(100),
HostName nvarchar(100),
BlkBy nvarchar(100),
DBName nvarchar(100),
Command nvarchar(100),
CPUTime int,
DiskIO int,
LastBatch nvarchar(100),
ProgramName nvarchar(100),
SPID2 int,
REQUESTID int)
insert into #tmp
exec sp_who2 active
select * from #tmp order by DiskIO desc
select * from #tmp where Login !='sa' order by LastBatch asc
select * from #tmp where Status !='sleeping' and Login !='sa'
select * from #tmp where DBName = 'ces_flextronics'
--dbcc inputbuffer(492)
create table #tmp
(SPID int,
Status nvarchar(100),
Login nvarchar(100),
HostName nvarchar(100),
BlkBy nvarchar(100),
DBName nvarchar(100),
Command nvarchar(100),
CPUTime int,
DiskIO int,
LastBatch nvarchar(100),
ProgramName nvarchar(100),
SPID2 int,
REQUESTID int)
insert into #tmp
exec sp_who2 active
select * from #tmp order by DiskIO desc
select * from #tmp where Login !='sa' order by LastBatch asc
select * from #tmp where Status !='sleeping' and Login !='sa'
select * from #tmp where DBName = 'ces_flextronics'
--dbcc inputbuffer(492)
Finding the Lead Blocker in a Set of Blocking Process Reports
Script 1:
---------------------------------
SELECT sc.session_id AS UserSPID,
sr.blocking_session_id AS BlockingSPID,
DB_NAME(st.dbid) AS DatabaseNm,
ss.program_name AS AccessingProgram,
ss.login_name AS LoginNm,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectNm,
CAST(st.text AS VARCHAR(MAX)) AS RequestSQL
--INTO #Processes
FROM sys.dm_exec_sessions ss
JOIN sys.dm_exec_connections sc ON ss.session_ID = sc.session_ID
LEFT JOIN sys.dm_exec_requests sr ON sc.connection_ID = sr.connection_ID
CROSS APPLY sys.dm_exec_sql_text (sc.most_recent_sql_handle) AS st
WHERE sc.session_id > 50 --gets us user processes
and isnull(sr.blocking_session_id,0) >0
Script 2:
---------------------------------
/*
--************************************************************************************
-- Description: This script list the current locking block chain.
-- It will create 1 function: fn_GetLockChainSubTree
-- 1 table: LockChain
--
--
-- Parameters: NONE
--
-- Returns: Locking block chain
--
-- Created: 9/07/2008 John Liu
--
-- Typical Usage: This script is typically used to find the block chain information
--
-- Comments: It also lists the dead lock (if any) at the end.
-- This script is for SQL 2005 and SQL 2008
--
--
-- Modifications:
--
-- Initials Date Description
-- -------- ---------- -----------------------------------------------------------
--
--************************************************************************************
*/
--***************************************Begin of create functions ********************************
--***************************************** Begin of dbo.fn_GetLockChainSubTree ************************
IF OBJECT_ID('dbo.fn_GetLockChainSubTree') IS NOT NULL DROP FUNCTION dbo.fn_GetLockChainSubTree
go
CREATE FUNCTION dbo.fn_GetLockChainSubTree(@Blocker AS INT)
RETURNS @TREE TABLE
(
Blocker INT NOT NULL,
Blocked INT NOT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Block_Subtree(Blocker,Blocked, lvl)
AS
(
-- Anchor Member (AM)
SELECT
Blocker
,Blocked
,0
FROM
LockChain
WHERE
Blocker = @Blocker
UNION all
-- Recursive Member (RM)
SELECT
l.Blocker
, l.Blocked
, t.lvl+1
FROM
LockChain AS l
INNER JOIN
Block_Subtree AS t
ON
l.Blocker = t.Blocked
)
INSERT INTO
@TREE
SELECT
*
FROM
Block_Subtree
RETURN
END
GO
--***************************************** End of dbo.fn_GetLockChainSubTree ************************
--***************************************End of create functions ********************************
--*************************************************************************************
--********************************** Main section **************************
--*************************************************************************************
-- -----------------------------------------------
-- Core processing
-- -----------------------------------------------
IF OBJECT_ID('LockChain') IS NOT NULL DROP TABLE LockChain
go
--initial the LockChain table
SELECT
Blocker = Blocking_Session_ID
,Blocked = Session_ID
INTO
LockChain
FROM
sys.dm_exec_requests
WHERE
Blocking_Session_ID <> 0
--list the locking chain
SELECT
t.*,s.host_name,s.program_name,s.login_name,s.nt_user_name
FROM
LockChain l
CROSS APPLY
fn_GetLockChainSubTree(l.Blocker) as t
INNER JOIN
sys.dm_exec_sessions s
ON
l.blocker = s.session_id
WHERE
l.blocker NOT IN (
SELECT
blocked
FROM
LockChain
)
--report any dead lock
SELECT
'Dead Lock' = 'Dead Lock'
,*
FROM
LockChain
WHERE
blocker IN (
SELECT DISTINCT
blocked
FROM
LockChain
)
AND
blocked IN (
SELECT DISTINCT
blocker
FROM
LockChain
)
--clean up
--DROP TABLE LockChain
--DROP FUNCTION dbo.fn_GetLockChainSubTree
-- -----------------------------------------------
-- End of Core processing
-- -----------------------------------------------
--***************************************** End of Main section **********************************
---------------------------------
SELECT sc.session_id AS UserSPID,
sr.blocking_session_id AS BlockingSPID,
DB_NAME(st.dbid) AS DatabaseNm,
ss.program_name AS AccessingProgram,
ss.login_name AS LoginNm,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectNm,
CAST(st.text AS VARCHAR(MAX)) AS RequestSQL
--INTO #Processes
FROM sys.dm_exec_sessions ss
JOIN sys.dm_exec_connections sc ON ss.session_ID = sc.session_ID
LEFT JOIN sys.dm_exec_requests sr ON sc.connection_ID = sr.connection_ID
CROSS APPLY sys.dm_exec_sql_text (sc.most_recent_sql_handle) AS st
WHERE sc.session_id > 50 --gets us user processes
and isnull(sr.blocking_session_id,0) >0
Script 2:
---------------------------------
/*
--************************************************************************************
-- Description: This script list the current locking block chain.
-- It will create 1 function: fn_GetLockChainSubTree
-- 1 table: LockChain
--
--
-- Parameters: NONE
--
-- Returns: Locking block chain
--
-- Created: 9/07/2008 John Liu
--
-- Typical Usage: This script is typically used to find the block chain information
--
-- Comments: It also lists the dead lock (if any) at the end.
-- This script is for SQL 2005 and SQL 2008
--
--
-- Modifications:
--
-- Initials Date Description
-- -------- ---------- -----------------------------------------------------------
--
--************************************************************************************
*/
--***************************************Begin of create functions ********************************
--***************************************** Begin of dbo.fn_GetLockChainSubTree ************************
IF OBJECT_ID('dbo.fn_GetLockChainSubTree') IS NOT NULL DROP FUNCTION dbo.fn_GetLockChainSubTree
go
CREATE FUNCTION dbo.fn_GetLockChainSubTree(@Blocker AS INT)
RETURNS @TREE TABLE
(
Blocker INT NOT NULL,
Blocked INT NOT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Block_Subtree(Blocker,Blocked, lvl)
AS
(
-- Anchor Member (AM)
SELECT
Blocker
,Blocked
,0
FROM
LockChain
WHERE
Blocker = @Blocker
UNION all
-- Recursive Member (RM)
SELECT
l.Blocker
, l.Blocked
, t.lvl+1
FROM
LockChain AS l
INNER JOIN
Block_Subtree AS t
ON
l.Blocker = t.Blocked
)
INSERT INTO
@TREE
SELECT
*
FROM
Block_Subtree
RETURN
END
GO
--***************************************** End of dbo.fn_GetLockChainSubTree ************************
--***************************************End of create functions ********************************
--*************************************************************************************
--********************************** Main section **************************
--*************************************************************************************
-- -----------------------------------------------
-- Core processing
-- -----------------------------------------------
IF OBJECT_ID('LockChain') IS NOT NULL DROP TABLE LockChain
go
--initial the LockChain table
SELECT
Blocker = Blocking_Session_ID
,Blocked = Session_ID
INTO
LockChain
FROM
sys.dm_exec_requests
WHERE
Blocking_Session_ID <> 0
--list the locking chain
SELECT
t.*,s.host_name,s.program_name,s.login_name,s.nt_user_name
FROM
LockChain l
CROSS APPLY
fn_GetLockChainSubTree(l.Blocker) as t
INNER JOIN
sys.dm_exec_sessions s
ON
l.blocker = s.session_id
WHERE
l.blocker NOT IN (
SELECT
blocked
FROM
LockChain
)
--report any dead lock
SELECT
'Dead Lock' = 'Dead Lock'
,*
FROM
LockChain
WHERE
blocker IN (
SELECT DISTINCT
blocked
FROM
LockChain
)
AND
blocked IN (
SELECT DISTINCT
blocker
FROM
LockChain
)
--clean up
--DROP TABLE LockChain
--DROP FUNCTION dbo.fn_GetLockChainSubTree
-- -----------------------------------------------
-- End of Core processing
-- -----------------------------------------------
--***************************************** End of Main section **********************************
Subscribe to:
Posts (Atom)