How to find out how much CPU a SQL Server process is really using

Have you ever think about kpid in SQL Server when you look into sysprocesses system table.Its very useful coloumn when you are dealing with CPU pressure.When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming most of the CPU. You launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure.
At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.
Find the below tip to get step by step process to identify a particular sql server process which is responsible for CPU pressure.

Tip: How to find out how much CPU a SQL Server process is really using

Enable Powershell Remoting on SQL Server Instances

Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? Yes It is..Find this MSSQLTips for step by step process to enable Powershell Remoting and access SQL Server Instances remotly.

Tip:- Enable Powershell Remoting on SQL Server Instances

ASYNC_IO_COMPLETION Wait type

Normally this wait type can be seen in backup and restore transactions.and whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource to proceed its operation and it will wait till certain time period then moved in suspended state. In that case your process will take more than its usual time to complete or most of the time it will hung or will showing in executing state for unknown time duration.

This wait type occurs when a task is waiting for asynchronous I/Os to finish. This wait type is normally seen with few other wait types like BACKUPBUFFER,BUCKIO etc. This is clear indication of DISK IO issue.You can also get the Average disk queue length or current disk queue length value at the same time when you are getting this wait type.Compare both counters and if these counters have high value then you should look into your storage subsystem. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.

Any of the following reduces these waits:

1.      Adding additional IO bandwidth.

2.      Balancing IO across other drives.

3.      Reducing IO with appropriate indexing.

4.     Check for bad query plans.

5.     Check for memory pressure

 We can also corelate this wait type between Memory pressure and Disk IO subsystem issues.

BACKUPBUFFER Wait Type

Today I was working on an issue in which backup job was showing in executing state since last 2 days.Initially my observation was either it was hung in middle of its process or it was processing very slow.Here i decided to see the lock wait type for this process.when i gather this info i see the wait type was BACKUPBUFFER wait type.

BACKUPBUFFER Wait Type:-This wait type Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount. This wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system.This wait type can be seen with one more wait type i.e. ASYNC_IO_COMPLETION. 

These both wait type indicates that the issue is with storage/disk subsystem.So solution is to either ask your storage team to look in this or change your disk/storage subsystem and run your backup on a healthy and fast storage subsystem.

Apple iPod touch 32 GB (4th Generation) NEWEST MODEL

RESOURCE_SEMAPHORE Wait Type

RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

High waits on RESOURCE_SEMAPHORE usually result in poor response times for all database users, and need to be addressed.

It is also useful to correlate high waits on RESOURCE_SEMAPHORE with the Memory Grants Pending and Memory Grants Outstanding SQL Memory Mgr performance counters. Higher values for these counters indicate a definite memory problem especially a non-zero value for Memory Grants Pending.

The root cause of this type of memory problem is when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory. The solution would be to tune the offending queries, or manage their workload so that they are executed at less busy times.

You can also get another article about sql server Wait type here:Sql server Wait Type

What is SQL Server Wait Type? or How to get wait type info in sql server?

In sql server 2005 and later version we can get this info from various DMVs.One of the most useful DMV is sys.dm_qs_wait_stats.This DMV gives you the detail report of all wait types and you can look into those wait types which are causing issue or waiting from lot much time.You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.Following columns will come in output when you run this DMV:

• wait_type :- Name of the wait type.
  waiting_tasks_count:-Number of waits on this wait type. This counter is incremented at the start of each wait.
• wait_time_ms:-Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
• max_wait_time_ms:-  Maximum wait time on this wait type.
• signal_wait_time_ms :-Difference between the time that the waiting thread was signaled and when it started running.

In general there are three categories of waits that could affect any given request:

• Resource waits are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted. Resource waits are the ones you should focus on for troubleshooting the large majority of performance issues.
• External waits occur when SQL Server worker thread is waiting on an external process, such as extended stored procedure to be completed. External wait does not necessarily mean that the connection is idle; rather it might mean that SQL Server is executing an external code which it cannot control. Finally the queue waits occur if a worker thread is idle and is waiting for work to be assigned to it.
• Queue waits normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted. Normally you don't have to worry about any performance degradation due to queue waits.

sys.dm_os_wait_stats shows the time for waits that have completed. This dynamic management view does not show current waits.If you want to see current wait type then you should use another system table sys.sysprocesses.Run below cmd:

Select * from sys.sysprocesses

The output describes you the wait time and wait type for each process id. So here you can get which process is pending since how much time and which type of wait is that.Once you have the process id then you can run below cmd to get the codes behind that SP id:

DBCC inputbuffer(SP ID)

 You can also compare DMV (sys.dm_os_wait_stats)output to this sysprocess table output and you can better analyze the exact issue.

A SQL Server worker thread is not considered to be waiting if any of the following is true:

    *      A resource becomes available.
    *      A queue is nonempty.
    *      An external process finishes.

Although the thread is no longer waiting, the thread does not have to start running immediately. This is because such a thread is first put on the queue of runnable workers and must wait for a quantum to run on the scheduler.

Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.

The contents of this dynamic management view can be reset by running the following command:
Copy

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

This command resets all counters to 0.

How to know db version or build no from a backup file? or How to get the exact version on which master db was backed up?

Generally we don't need this info for user db but when you have to recover or restore master db then you should have exact db version or build no else you cannot recover your db like i am giving you a scenario suppose you instance is corrupted now you are not able to access your sql server instance and even you don't have exact db version like 9.00.**** In this case you need exact build no else you can not recover your sql server Instance.To get this info you need a master db backup file.You can run below cmd to get this info:

Restore headeronly from disk='D:\MSSQL\Backup\master.bak'

Now output will show you lot much info like user name,LSNs,device type,server name etc but you should look into below three coloumns  to get exact version no of sql server.As per this output the version on which master db was backed up is 10.50.1600.Now you got the exact build so install sql server till this build no and restore master db easily.

SoftwareVersionMajor  SoftwareVersionMinor  SoftwareVersionBuild
10                                  50                                   1600




You can get another article regarding how to restore master db in this blog as well.Kindly find the link:Restore master db

How to recover or repair a suspect database in sql server

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

You can run below cmd to get about all errors due to whihc db went in suspect mode:

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

Once you have enough info about errors then your next step should be Repair.If error is showing datafiles missing then check your datafile drive connectivity from storage side if its missing then you dont need to do anythig just contact someone from storage or one who manages storage in your env and ask them to check data file drive is healthy or not.

And if there is another issue then you can repair your database from below cmds:

SP_RESETSTATUS [DBNAME]
go
ALTER DATABASE [DBNAME] SET EMERGENCY
go
ALTER DATABASE [DBNAME] SET SINGLE_USER;
go
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
ALTER DATABASE [DBNAME] SET Multi_USER;

Put your db name in place of [DBNAME] in above script.

Note:-REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

"Error 14258: Cannot perform this operation while SQL ServerAgent is starting. Try again later."

I saw this error today while i was diagnosing my log shipping issue in which primary and standby servers was not in sync.i checked log but i didn't find anything even Sql agent was running and all jobs was showing as successful.

Then i tried to bounce Agent service on primary node then i got below error:

"Error 14258: Cannot perform this operation while SQL Server Agent is starting. Try again later."

To fix this issue, I tried stopping and then restarting just the Agent
service and it won't start, reporting the same error then my guess was that SQL Server was running in lightweight pooling mode. I set it back to the default of
thread mode.

sp_configure 'allow updates', 1
go
reconfigure with override
go
sp_configure 'lightweight pooling', 0
go
reconfigure with override


And after this change it worked for me..

How to disable Auto Commit in SQL Server

You can turn auto commit OFF by setting implicit_transactions ON:

SET IMPLICIT_TRANSACTIONS ON

When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.
And when you need to enable it just run above cmd with OFF clause.

SET IMPLICIT_TRANSACTIONS OFF

autocommit is the default for Sql Server 2000 and up.

Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Whenever you get this issue make sure your security context is correctly supplied.In that case you have to use domian user acount in sp_addlinkedserver stored procedure whihc has access rights to Active Directory.The domain name is required in the Security settings for the remote login. So in the Security settings I entered Remote login: domain\user; With password: password.Find below dummy example.

Step1:-EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'LINLDP2'

Output : Success

Step2: EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false', @rmtuser='Domainname\username', @rmtpassword='#Security@SWIM#'

Also,Make sure your SQL Server Service is running under the account which has access right to Active Directory.

How to get, when your db was restored last time?

Suppose you have to findout when you db was restored last time and by whihc user.To get this info you have to look into a system table 'restorehistory' whihc exist in system database msdb.Run below cmds to get this info:

use msdb
go
select Destination_database_name AS [DB Name],user_name AS [User] ,restore_date As [Last Restore Date]
from restorehistory
where Destination_database_name like ('qa%')

Once you will run this you will get below output.

To find out no of traces running on your db instance

Suppose you want to find out how many traces are running on your instance?
No idea???here is a solution to find out no of traces, also you can stop them by below script.

To Find out the number of traces running use the below query

SELECT * FROM fn_trace_getinfo(default);
GO
You will get below output:

You can find out the no of traces through trace ID coloumn/count.Now suppose you have to stop them then run below script:

DECLARE @TraceID int
SET @TraceID = 2
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

Replace the trace id value with one which has to be stopped.

Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

Fix/Solution/Workaround:

Follow below steps:-
1. Move mdf and ndf files to another directory (Data_old)
2. Create a database with the same name and same file names and locations as the original databases. (this only applies to the mdf and ndf files the log file can go anywhere)
3. Stop the SQL Server service.
4. Overwrite new mdf and ndf files with the original ones.
5. Start SQL Server.
6. Run this script (Set the @DB variable to the name of your database before running):
Declare @DB sysname;
set @DB = ‘DBName’;
– Put the database in emergency mode
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);
– Set single user mode
exec sp_dboption @DB, ‘single user’, ‘TRUE’;
or
– Repair database
DBCC checkdb (@DB, repair_allow_data_loss);
– Turn off single user mode
exec sp_dboption @DB, ‘single user’, ‘FALSE’;

If you are not able to connect after single user then run below cmd in one go.

Alter database dbname set single_user with roll back immediate
go
Run your DBCC checkdb command
go
Alter database dbname set multi_user

I got an error stating that the log file did not match the data file. You can ignore this as we are rebuilding the log file.

BIDS is not working after SQL Server Installation or BIDS is not installed properly.

First point is to make sure that Visual Studio is installed or not. If not previously have Visual Studio installed then first install it from SQL Server setup or run below msi or exe file:

*.\Tools\Setup\vs_setup.msi

This will install the VS Shell. After this is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory:

start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS

If above cmd gives any error then run sqlRun_tools.msi (Root\tools\setup\sqlRun_tools.msi))setup before running above cmd.Once this will complete then proceed once again with above cmd.