Monday, November 15, 2010

SQL Server Integration Services (SSIS) Performance Best Practices

http://www.mssqltips.com/tip.asp?tip=1840

http://www.mssqltips.com/tip.asp?tip=1867

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

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

UPDATE STATISTICS

sp_MSforeachtable'UPDATE STATISTICS ?;'

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

Thursday, September 30, 2010

sqlserverpedia.com - powered by Quest

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)