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

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]

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.

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

Friday, October 22, 2010

SQL 2008 Data Collection

Data Collection

Manually enable data collection
Managing Data Collection Using Transact-SQL

SQL Server 2008 Data Collector – Video Tutorial

Data Collector: Remove Data Collector to Remove Associated Objects

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

Friday, October 15, 2010


sp_MSforeachtable'UPDATE STATISTICS ?;'

Friday, October 01, 2010

Custom Reports Library - SQL Server

SQL Server Manageability Team Blog