Data and Analytics

Use below code to find tables with most reads. This is a very useful DMV available in SQL2005 onward while doing a performance troubleshooting on the database. Use <DBNAME> -- Replace DBNAME with the Database name  DECLARE @dbid int SELECT @dbid = db_id() SELECT TableName = object_name(s.object_id), Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates) FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.database_id = @dbid GROUP BY object_name(s.object_id) ORDER BY reads DESC

There are multiple way to find this information. However I use one of the below options: Option 1: Find this information from SQL Server Error Log. Option 2: Find when tempdb is created. A simple command as sp_helpdb 'tempdb' will give this information. We can conclude that this time/date (when tempdb was created) is the last start-up time of SQL Server. Option 3:   Please use below code: SELECT sqlserver_start_time from sys.dm_os_sys_info However please note that this feature is available only from SQL Server 2008 onwards.

This error is typically logged with Windows Application Log with below details: Event Type: Error Event Source: MSOLAP$INSTANCENAME Event Category: (289) Event ID: 3 Date: DATE Time: TIME User: N/A Computer: SERVERNAME Description: OLE DB error: OLE DB or ODBC error: Timeout expired; HYT00. However most likely you will not get a log in SQL Error Log. Typically this error is caused because when some application faces time out error from SQL Server for it’s setting. This is normal and does not need any remediation unless this is a repetitive symptom. In case of a repetitive symptom, it can be assumed that some application/users is executing long query which is exceeding maximum time allowed per server setting and preventive measurements may be taken as needed. 

Please execute below code to drop a database snapshot: USE [master] GO DROP DATABASE [<Snapshotname>] GO

Use below code to create a database snapshot: CREATE DATABASE <SnapshotName> ON ( NAME = <Logical_FileName1>, FILENAME = 'SnapshotFile1'), --eg. X:\Snap1.ss (NAME = <Logical_FileName1>, FILENAME = 'SnapshotFile1') AS SNAPSHOT OF <SOURCEDatabase>; GO Note! 1) You must provide snapshot file name for all the logical files of the source database.  Snapshot is a readonly copy of the source database at the given time when snapshot was created. 2) You must be careful about keeping snapshot for long time of a big busy (VLDB/OLTP) database. This can cause space issue and also performance issue. Please go through the architecture of this technology and sparse file before implementing it.