Tags » Database Design - SQL Server 2008, 2008R2 & 2012

Tips and Tricks to avoid common pit falls with SQL CLR deployment

  1. If you are using Visual Studio to build and deploy the SQL CLR solution ensure you have below settings

2) First things first, Ensure CLR Integration is enabled at instance level. 232 more words

Database Design - SQL Server 2008, 2008R2 & 2012

Clone your SQL Database instantly with new DBCC command CLONEDATABASE

Based on MS documentation this is a quick way to create a copy of database (only Schema) including statistics and Indexes of source database this was released in SQL 14 service pack 2. 193 more words

Database Design - SQL Server 2008, 2008R2 & 2012

High CPU Usage SQL Server (One Bad Query)

My colleague reported to me that one of our database server is reporting consistent high CPU usage so I looked at it I noticed CPU was at 100% from last one week when I contacted the application owner and I foundthat they implemented a new feature that polls the database for every second to ensure the data collection process is running properly as it was necessary to ensure that we are under compliance in terms of reporting and auditing.  200 more words

Database Design - SQL Server 2008, 2008R2 & 2012

Comparing sys.dm_db_index_physical_stats mode levels with Heap and Clustered Index

Create a table with some data

drop table t3
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
--BEGIN TRAN
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
BEGIN
BEGIN TRAN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx+convert(int,rand() * 100), 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
	COMMIT
END
--COMMIT;
GO

…
446 more words

Administration