How to improve TempDB performance?
1) TempDB file size and physical placement on harddrive can affect the performance of a system
2) Set the tempdb recovery model to simple
3) Do the capacity planning of the TempDB database and pre allocate the appropriate size to MDF files, which will improve performance for allocating size
4) Put TempDB on fast I/O System
5) Create as many tempdb data files by considering the number of cpu
6) make all the tempdb data file size same
How to search error from SQL Server error log or SQL Server Agent Log?
We can find the particular error or information from error log by passing below parameters to XP_ReadErrorLog extended procedure.
EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
@P1 = Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
@P2 = Log file type: 1 or NULL = error log, 2 = SQL Agent log
@P3 = Search string 1: String one you want to search for
@P4 = Search string 2: String two you want to search for to further refine the results
Please note all the parameters are optional.
You can use the SQL Server Enterprise Manager for SQL Server 2000 to set a database to a single user mode or to multi user mode. Similarly, SQL Server Management Studio can be used for SQL Server 2005 for changing a database to single user mode or to multi user mode. Also you can alter the database access mode by using sql commands like ALTER DATABASE and sp_dboption.
ALTER DATABASE [MyDBName] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [MyDBName] SET SINGLE_USER WITH NO_WAIT
or
EXEC sp_dboption ‘MyDBName’, ‘single user’, ‘false’
EXEC sp_dboption ‘MyDBName’, ‘single user’, ‘true’
Find the SQL Server Service Startup Time
Many times for collecting or analyzing performance counter from the different DMV we need to collect the data of many days, so at that it is necessary to find the SQL Server Service startup time. We can find it as below.
1) Every time when SQL Server restarts, It creates the “TempDB” so from the TempDB creation time you can find SQL Server startup time.
SELECT CREAT_DATE FROM SYS.DATABASES WHERE NAME = ‘TEMPDB’
2) If you error log is not flushed you can use the XP_ReadErrorlog and from there you can find the SQL Server startup time
3) We can also find it using sys.dm_exec_requests DMV for startup time
4) We can also find it from Master..SysProcesses system table by analyzing the login time for the System Processes (for i.e. LAZYWRITER)
When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. This will help database administrator to check the different user permissions. `Execute As` is also very helpful when a user wants to execute a stored procedure in the context of another user.
Syntax
Execute as user = ‘SQL_JShah’
select * from CompanyProducts.Products
