Logging to Event Viewer


SQL Server exposes 2 methods of Logging to the Windows Application log using T-SQL out of the box: xp_logevent and RAISEERROR. Both are simple straight forward and convenience to use but lack the option to control the Event Id passed to the log entry we add. Both xp_logevent and raiseerror add entries with EevntIDs 17061 and 17063 for Information and Error respectively and you have no control over the EevntId value. In order to unify all messages from the various sources into a single centralized location which in this case is the Windows Event Viewer being monitored by...
no comments

Script entire server permissions


This script is very useful when you have to copy permissions from one instance of SQL Server to another. For example when you perform a side by side upgrade or when you replace the server HW and need to duplicate all stuff. Run the script on the source server (the server you are about to replace) and copy save the output so that you can run it on the target instance (the new instance that will replace the current instance). Before running the Permissions script you need to handle logins and the best option for that is to use...
no comments

Script attach and detach commands for all databases


This script (coded by a colleague DBA Boaz Goldstein) generates the attach and detach commands for all databases on the instance in an xml format to overcome the SSMS limitation of results to Text (limited to 8mb) or results to Grid (limited to 64mb). When returning a result in the format of xml SSMS limits you to 2gb (not really a limitation) which is the max LOB size. You come across this limitation when working with large databases spanning multiple file groups and data files and then your script becomes big and gets cut in the middle Copy...
no comments

Get statistics of Stored Procedures execution using SQL Trace


Today I was assigned the task of generating a report showing statistics of Stored Procedures execution. My customer needed that data because they have heavy processes preparing data to be ready for quick retrieval by user reports. There are many reports and we wanted to know if there are reports that are not being used and so we can stop preparing data for these reports. We can get statistics information of stored procedures executions from the sys.dm_exec_procedure_stats DMV which has valuable information but has some limitations, for example a stored procedure defined using WITH RECOMPILE will not have any...
no comments

Using SQL IO to test InfiniBox performance – Updated


Following the IO test results I published in this post the guys from Infinidat have contacted me and made me aware that the test result do not really reflect the performance capability of the machine and this is so because the numbers I got indicate that the limitation was at the host side and not at the SAN. They investigated the SAN logs from the time of my test and showed that we have reached the limitation of the HBA card at the host. The host we tested had a dual port 4gb HBA card which means it can...
no comments

Automate Manual Update of Statistics


SQL Server by default has the Boolean database properties “Auto Update Statistics” and “Auto Create Statistics” turned on and as the name implies SQL Server self maintains database statistics and does a pretty good job as well. But, there are cases where the “Auto Update Statistics” database option is just not good enough and the DBA has to take some action to improve on the current state and before I proceed here is a brief explanation I have copied from an older post of mine The source to the problem is that statistics are updated when 20% (+ 500...

Get statistics of stored procedures executions from sys.dm_exec_procedure_stats


sys.dm_exec_procedure_stats can provide some interesting and useful statistics data about stored procedures executions. I feel this DMV is underused and I have to admit I also seem to forget of it’s existence but from time to time when I visit a customer for the first time and I need to generate a baseline report of executions I use this DMV (where as my default action would be to use SQL trace and set a stop time of 60 minutes). An alternative would be to capture SQL Trace or use XE. These 2 methods would provide accurate statistics data but...
no comments

Synchronize AlwaysOn Replicas Configuration and Server Level Objects (Logins, Permissions, Jobs….)


AlwaysOnSynchronizer is a free .NET console application that is designed to be run by an SQL Server Agent job and address the issue of synchronizing “server level objects” from the primary replica to the secondary replica(s). Here is a direct download link to AlwaysOnSynchronizer from the sqlserverutilities site. The utility comes in to address the need where environments that have implemented AlwaysOn Availability Groups need to synchronize “server level objects” that are beyond the scope of the Availability Databases that actually synchronize the databases only but do not synchronize objects outside the scope of the database. This leaves objects...
no comments

Use sys.dm_db_stats_properties to get statistics information


The sys.dm_db_stats_properties DMF has been introduced in SQL Server 2008 R2 sp2 and SQL Server 2012 sp1 and offers a much more detailed view over the statistics objects. Prior to this DMF there was no real way to get all that information about statistics which is required if you would like to update statistics manually based on the number of modifications done on the table since the last update of statistics took place. Because the information was not available I have seen some implementations of DBAs manually updating statistics based on the last statistics modification date but this is...

A new update to sp_helpindex2 is now released

The update fixes a bug where on a partitioned table the partitioning key was returned as part of the index keys even though it was not an index key. In addition I added 2 new columns to the output. The first column shows the data_compression description and the second column shows the number of partitions in the table. I also modified slightly the way the index size was calculated which had a minor inaccuracy.   To download sp_helpindex2 go to the original post here -Yaniv
no comments