Dosql

SQL Server Best Practices

Harry Zheng — Sun, 11 Apr 2010 06:39:24 +0000

http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

Setup SQL trace to capture deadlock

sales — Sun, 11 Apr 2010 00:16:21 +0000

Steps to create a SQL trace for deadlock Create a trace by using sp_trace_create. Add events with sp_trace_setevent. Set a filter with sp_trace_setfilter. Start the trace with sp_trace_setstatus. Stop the trace with sp_trace_setstatus. Close the trace with sp_trace_setstatus. – Create a trace declare @rc int declare @TraceID int declare @maxfilesize bigint = 5 exec @rc = sp_trace_create @TraceID output, 0, N’C:\Temp\DeadlockTrace1′, @maxfilesize, NULL if (@rc = 0) Begin – Add [...]

Database Design Tip

sales — Fri, 09 Apr 2010 07:13:22 +0000

Start with solid logical and physical design Frequent select distinct or union serves as a warning that design may have issue Normalize database for OLTP Consider denormalization to reduce joins Be careful about denormalization, use as last resort nulls in fixed length column? One large database v.s. several smaller databases Partitioning large tables Leave format in field or remove format Design to reduce user contention Allow [...]

SSIS calling stored procedure with uniqueidentifier data type parameter issue

Harry Zheng — Fri, 09 Apr 2010 06:27:57 +0000

When calling a SP to retrieve data set from SSIS designer, there is no uniqueidentifier data type to map the parameter of the SP. So I choose text data type instead, however the query designer can’t execute with an “error converting text to uniqueidentifier” message. Not sure if this is a bug. The walk around is [...]

Log PerfMon to Database error

Harry Zheng — Tue, 30 Mar 2010 21:18:44 +0000

I tried to log PerfMon log to a data source, but I am getting an error. This is found in Windows event log: The description for Event ID 3042 from source Microsoft-Windows-PDH cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install [...]

Find Unused Indexes in SQL Server Database

Harry Zheng — Sun, 28 Mar 2010 16:44:15 +0000

Ever wondering if you indexes are been used at all? This script will tell you: Select row_number() Over (order by S.name, object_name(IUS.object_id),I.name) as UnusedIndex ,S.name as SchemaName ,object_name(IUS.object_id) as TableName ,I.name as IndexName ,I.index_id From sys.dm_db_index_usage_stats IUS Inner Join sys.indexes I on IUS.object_id = I.object_id And I.index_id = IUS.index_id Inner Join sys.objects O on I.object_id = O.object_id Inner Join [...]

Travel plan for a long long while

Harry Zheng — Sun, 28 Mar 2010 16:03:03 +0000

Varadero, Cuba Paris, France London, UK Create your own travel map or travel blog Vacation rentals at TripAdvisor

Office SharePoint Server Error: EventType ulsexception12, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 system.data, P5 2.0.0.0, P6 461ef1ad, P7 1999, P8 7, P9 constraintexception, P10 0000.

Harry Zheng — Wed, 10 Mar 2010 17:40:12 +0000