- What are differences between a latch and a lock?
- What are the different states of an spid in SQL Server? Explain each state.
- What are workers in a SQL Server?
- How to reduce network usage by a query?
- Explain SOS_Scheduler_Yield wait type.
- What are CXPACKET waits? how to avoid that?
- Difference between a VAS and buffer pool?
- What are the different components of a SQL Engine? Explain how a SQL query is processed by SQL Server (Query lifecycle)?
- What are the different components of an RDBMS Engine?
- What are primitive and non-primitive way of DBMS functioning? In which type does the SQL server belong to?
- What are Memory Clerks?
- Explain SQLOS.
A few months back, I had shared a script to automate index rebuild/reorg in a database with fragmentation analysis inplace at http://www.sherbaz.com/2013/06/resumable-auto-index-rebuild-and-reorganize-script/. But that script would perform the maintenance only on a particular database at a time.
The script is modified and it now performs maintenance on all indexes across all databases in an instance. This again has two scripts.
1. analyze2.sql – Checks fragmentation level on indexes in all databases of the SQL instance and stores the results in tempdb.
2. defrag2.sql – Based on the data stored in tempdb by analyze2.sql, the script will process the maintenance, either Index rebuild or an index reorganize based on the fragmentation and the @minfrag, @maxfrag settings in defrag.sql script. Indexes fragmented below @minfrag in percentage will be excluded. Indexes fragmented between @minfrag % and @maxfrag % will be reorganized. All remaining indexes will be rebuilt.
Scripts are pasted below for your reference. Please let me know if you find some errors, bugs or issues with the code or the method used.
In all SQL server failover clusters in our environment, It came to our notice that the SQL Server Agent is spamming the Application eventlogs under event viewer below entries every second in the Active node.[sqagtres] LooksAlive request. [sqagtres] CheckServiceAlive: returning TRUE (success)
It was observed that this incident started to occur after the scheduled windows patching.
Microsoft had identified this as due to a code defect in Sqagtres.dll. To resolve this issue, we would have to patch the SQL instance with Cumulative update 3 for SQL Server 2008 R2 SP2 as mentioned in below KB.
The hotfix download is available at http://support.microsoft.com/kb/2754552
We could easily grant execute permission to a user on all stored procedures on a database as below.
USE <database name>
GRANT EXECUTE TO <user>
But unfortunately, our database will be at risk since the user would have got access to almost all stored procedures including the system stored procedures in that database. Hence use below steps to grant access to non-system stored procedures securely.
- Run below script on the database and copy the output.
1234567USE <database name>GOSELECT 'EXEC(''GRANT EXECUTE ON'+OBJECT_SCHEMA_NAME(object_id)+'.' + name + 'TO [<domain\user>]'')'from sys.procedures ORDER BY nameGO
- Paste the output to a new query window.
- Add “USE <database name>” at the beginning of the pasted output code and execute.
Note: Values inside ‘<‘ and ‘>’ has to be replaced with your actual database name and user information where ever required.
One fine morning, users started complaining my SQL server instance performance…They sent me a screenshot which indirectly says, their queries have timed out. I quickly logged in and checked if there are any blocks.
WHERE blocked <> 0
There were several blocks caused by a naughty spid 80.
When I checked DBCC INPUTBUFFER(80), To my surprise, I got below scary result.
FROM [dbo].[alldocversions] v
JOIN [dbo].alldocs d
ON v.id = d.id
GROUP BY v.id,
ORDER BY 4 DESC
reservedpages = Sum(a.total_pages),
usedpages = Sum(a.used_pages),
reservedMB = ( Sum(a.total_pages) * 8 / 1024 ),
usedMB = ( Sum(a.used_pages) * 8 / 1024 ),
pages = Sum(CASE
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
rows = Sum(CASE
WHEN ( p.index_id < 2 )
AND ( a.type = 1 ) THEN p.rows
FROM sys.objects o
JOIN sys.partitions p
ON p.object_id = o.object_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE o.type = ‘U’
GROUP BY o.name
ORDER BY 3 DESC
Use below queries one by one and hopefully, you will be able to find and fix your Database Mail issue.
- sysmail_help_queue_sp @queue_type = ‘Mail’
- select * from sysmail_event_log
- select * from sysmail_allitems
- select is_broker_enabled from sys.databases where name=’msdb’
As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.
To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.
Detaching a database with drop all connections enabled was running continuously without completing. When checked what was causing the block or lock, I found the Kill/Rollback was waiting with waittype “preemptive _os_pipeops”.
With some googling, I came to know that it is something to do with a process running at the operating system level. So the solution was to end any process like wscript or cscript or cmd running with sql server service account or whatever you think might be the process that used the SQL Database.
In my case, it was a vbscript running as wscript process running with SQL server service account. I found that in Windows task manager.