http://www.mssqltips.com/tip.asp?tip=1840
http://www.mssqltips.com/tip.asp?tip=1867
Monday, November 15, 2010
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)
Subscribe to:
Posts (Atom)