Performance dashboard in SQL Server Management Studio

Amazingly enough I have just learned for a new very convenient way of investigating bad performance. Usually when someone says “My SQL is running very slowly” I log on to the server and start looking at usual Task manager, Activity Monitor and of course – running a bunch of queries against system DMVs and decoding whatever info you find relevant. Well…shame on me Smile There are quite cool SQL Server management reports published just for that purpose and they go under the name  of SQL Server Performance Dashboard reports. They are released for SQL Server 2005 and you can download them from this link.

You can also use them with SQL Server 2008 but with some additional edit of the setup script. And all is very well documented here.

And for those of you who do not enough time to play with it on their own, there is a video that shows pretty much everything you need to know for Performance Dashboard – from setting it up, through using it and performing analysis.

Of course this reports are just providing you the data. You will have to interpret it yourself and turn it into proper information so you can react adequately to the situation Smile

Happy reporting and troubleshooting!

Advertisements

SQL Server performance and troubleshooting tools, tips and tricks

I’ve planned to write a series of post regarding different tools which you can use for performance analysis and troubleshooting but tonight I found that there is already such series posted. I have to say it is pretty neat and has a lot of amazing things to read and get to know. This includes RML Utilities, XPerf, Process monitor, Debug diagnostics and Crash rules. So instead of covering pretty much the same, I’d like to share the round-up post of the series so you can enjoy it.

Troubleshooting SQL: Tools Tips and Tricks round-up

I can only add to this that you can also consider PAL 2.0 (PAL@Codeplex) which  can help you directly analyze performance counters logs and prepare a template for counter collection. Also there is one more thing that I completely love about this tool – almost all counters have thresholds defined according to best practices and added explanation with reference links for each counter. The tool is pretty straight forward and after analysis it provides you with a report with marked problematic areas (IO, CPU, Memory or network).

So get introduced to the post I shared with you and all the tools, tips and tricks it provides and have a look at PAL 2.0 Tool which I think you will find very useful!

How to check who performed a SQL Server database backup

In this post I would like to share a simple way of extracting latest database backup including the user that has initiated it. Generally SQL Server engine does not store such info unless you have some custom piece of code (such as triggers) or you are using auditing in SQL Server 2008 and above. As you know each backup triggers an event that is stored in the application log of the operating system. This event includes the message that is written in the SQL Server error log and additionally has an user name, that triggered the event. Based on that and using PowerShell here is a simple list that returns newest 2000 backup events (full, differential or log) for all databases, sorting them descending by time. Columns returned are Time (the time the backup completed), Username (the user performed the backup) and Message(the message, which includes the type of backup and the database).

Get-EventLog "Application" -newest 2000 | Where-Object {$_.EventID -eq 18264 -or $_.EventID -eq 18265 -or $_.EventID -eq 18270}  |sort -descending TimeWritten |
Format-Table TimeWritten,UserName,Message -auto

As you can see I am querying directly the OS Application log and filtering three specific events:

  • EventID 18264 – Database full backup event
  • EventID 18265 – Database log backup event
  • EventID18270 – Database differential backup event

Also keep in mind that Backup and Restore events are not written to SQL Server Error log and NT Application event log if trace flag 3226 is enabled. So before trying to find out who performed a certain backup, check if those events are logged at first placeSmile

How to extract SQL Server non-default configuration settings

Have you ever wondered how easily to extract non-default SQL Server instance settings? Well recently I had to perform such task so I can apply those changed settings to a new instance that was replacing the current one. And the only thing that came to my mind was to extract all settings in Excel and then compare them one by one with the settings of this new instance. Later on when I was browsing in SSMS reports I noticed that in the “Server Dashboard” report there was such section. It was definitely not that visible and I felt quite lucky I found it.

image 

If you have followed my posts, not long time ago I posted a link to SSMS report sources. So far so good, however it was not very comfortable for me to have the configurations without being able to export them (the report can be exported only printed and the table is not selectable at all. So I needed a better way of finding and after that – storing those non-default configuration settings. I checked the source of the Server Dashboard report and I was surprised how simple that was…it was consisting of INSERT section and one simple join which was using data from sys.configuration. Not that big of a deal, you will say, however if you do not know it, it is quite a big deal actually Smile

So there is the script you can use (it is straight extract from the report source):

begin try
declare @configurations_option_table table (
        name nvarchar(128)
,       run_value bigint
,       default_value bigint 
);
declare @sp_configure_table table (
        name nvarchar(128)
,       minimum bigint
,       maximum bigint
,       config_value bigint
,       run_value bigint 
);
declare @tracestatus table(
        TraceFlag nvarchar(40)
,       Status tinyint
,       Global tinyint
,       Session tinyint
);

insert into @sp_configure_table 
select name
,       convert(bigint,minimum)
,       convert(bigint,maximum)
,       convert(bigint,value)
,       convert(bigint,value_in_use) 
from sys.configurations  

insert into @configurations_option_table values('Ad Hoc Distributed Queries',0,0)
insert into @configurations_option_table values('affinity I/O mask',0,0)
insert into @configurations_option_table values('affinity mask',0,0)
insert into @configurations_option_table values('Agent XPs',0,0)
insert into @configurations_option_table values('allow updates',0,0)
insert into @configurations_option_table values('awe enabled',0,0)
insert into @configurations_option_table values('blocked process threshold',0,0)
insert into @configurations_option_table values('c2 audit mode',0,0)
insert into @configurations_option_table values('clr enabled',0,0)
insert into @configurations_option_table values('cost threshold for parallelism',5,5)
insert into @configurations_option_table values('cross db ownership chaining',0,0)
insert into @configurations_option_table values('cursor threshold',-1,-1)
insert into @configurations_option_table values('Database Mail XPs',0,0)
insert into @configurations_option_table values('default full-text language',1033,1033)
insert into @configurations_option_table values('default language',0,0)
insert into @configurations_option_table values('default trace enabled',1,1)
insert into @configurations_option_table values('disallow results from triggers',0,0)
insert into @configurations_option_table values('fill factor (%)',0,0)
insert into @configurations_option_table values('ft crawl bandwidth (max)',100,100)
insert into @configurations_option_table values('ft crawl bandwidth (min)',0,0)
insert into @configurations_option_table values('ft notify bandwidth (max)',100,100)
insert into @configurations_option_table values('ft notify bandwidth (min)',0,0)
insert into @configurations_option_table values('index create memory (KB)',0,0)
insert into @configurations_option_table values('in-doubt xact resolution',0,0)
insert into @configurations_option_table values('lightweight pooling',0,0)
insert into @configurations_option_table values('locks',0,0)
insert into @configurations_option_table values('max degree of parallelism',0,0)
insert into @configurations_option_table values('max full-text crawl range',4,4)
insert into @configurations_option_table values('max server memory (MB)',2147483647,2147483647)
insert into @configurations_option_table values('max text repl size (B)',65536,65536)
insert into @configurations_option_table values('max worker threads',0,0)
insert into @configurations_option_table values('media retention',0,0)
insert into @configurations_option_table values('min memory per query (KB)',1024,1024)
insert into @configurations_option_table values('min server memory (MB)',0,0)
insert into @configurations_option_table values('nested triggers',1,1)
insert into @configurations_option_table values('network packet size (B)',4096,4096)
insert into @configurations_option_table values('Ole Automation Procedures',0,0)
insert into @configurations_option_table values('open objects',0,0)
insert into @configurations_option_table values('PH timeout (s)',60,60)
insert into @configurations_option_table values('precompute rank',0,0)
insert into @configurations_option_table values('priority boost',0,0)
insert into @configurations_option_table values('query governor cost limit',0,0)
insert into @configurations_option_table values('query wait (s)',-1,-1)
insert into @configurations_option_table values('recovery interval (min)',0,0)
insert into @configurations_option_table values('remote access',1,1)
insert into @configurations_option_table values('remote admin connections',0,0)
insert into @configurations_option_table values('remote login timeout (s)',20,20)
insert into @configurations_option_table values('remote proc trans',0,0)
insert into @configurations_option_table values('remote query timeout (s)',600,600)
insert into @configurations_option_table values('Replication XPs',0,0)
insert into @configurations_option_table values('RPC parameter data validation',0,0)
insert into @configurations_option_table values('scan for startup procs',0,0)
insert into @configurations_option_table values('server trigger recursion',1,1)
insert into @configurations_option_table values('set working set size',0,0)
insert into @configurations_option_table values('show advanced options',0,0)
insert into @configurations_option_table values('SMO and DMO XPs',1,1)
insert into @configurations_option_table values('SQL Mail XPs',0,0)
insert into @configurations_option_table values('transform noise words',0,0)
insert into @configurations_option_table values('two digit year cutoff',2049,2049)
insert into @configurations_option_table values('user connections',0,0)
insert into @configurations_option_table values('user options',0,0)
insert into @configurations_option_table values('Web Assistant Procedures',0,0)
insert into @configurations_option_table values('xp_cmdshell',0,0)

insert into @tracestatus exec('dbcc tracestatus')
update @tracestatus set TraceFlag = 'Traceflag ('+TraceFlag+')'



select 1 as l1
,       st.name as name 
,       convert(nvarchar(15),st.run_value) as run_value
,       convert(nvarchar(15),ct.default_value) as default_value
,       1 as msg  
from @configurations_option_table ct 
inner join  @sp_configure_table st on (ct.name = st.name  and ct.default_value != st.run_value)
union
select 1 as l1
,       TraceFlag as name
,       convert(nvarchar(15), Status) as run_value
,       '0' as default_value
,       1 as msg  
from @tracestatus where Global=1 order by name
end try
begin catch
select -100 as l1
,       ERROR_NUMBER() as name
,       ERROR_SEVERITY() as run_value
,       ERROR_STATE() as default_value
,       ERROR_MESSAGE() as msg
end catch

SQL Server CMS – where are locally registered servers stored

In one of my previous posts I showed you how to register multiple instances in SQL Server Management Studio Registered Servers explorer. However registering them locally has its pitfalls such as not being accessible for other DBAs without being imported/exported from the GUI. This operation is not that complicated itself, but it got me curious where are those registered instances stored after all so I do not have to export and import anything. It took me a bit longer to find it, but I did it. So, if you are curious too, those instances are stored in an XML file stored on the following paths:

Windows Server 2003 based systems: C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml

Windows Server 2008 based systems: C:\Users\<user>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml

The XML file is structured just as the one, created after exporting registered servers from the GUI. So you can just copy it and restore it wherever you desire.

List all full-text search catalogues on an instance

With the following script you can easily find all FTS catalogues and their state on the SQL Server instance.

  1. select sd.name as database_name,fts.name as fts_name,fts.catalog_id,
  2.        fts.is_importing,fts.is_paused,fts.status_description
  3. from sys.dm_fts_active_catalogs fts
  4. inner join sys.databases sd
  5.       on fts.database_id = sd.database_id

This could come handy when you are migrating a database as you should take care of rebuilding all FTS catalogues and for that – you will need a list of what should be rebuilt.

Error: 30064, Severity: 17, State: 1 in SQL Server Error log

Recently I came across a strange message in SQL Server Error Log:

SQL Server failed to set security information on the full-text FilterData directory in the FTData folder. Full-text indexing of some types of documents may fail until this issue is resolved. You will need to repair the SQL Server installation.”.

 

The error number was 30064 with Severity 17. After a quick check in the web I did not find any information of info on troubleshooting such error and I was definitely was not up for “repairing” my SQL Server installation as the message suggested.

To resolve the problem, check the FTData folder path set in the registry – is the path valid, does the SQL Server Service has enough permissions on that folder, etc. The path to the registry key is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance id>\Setup\FullTextDefaultPath

In my case it was an invalid path in that folder. Unfortunately I was not aware of how this value got there after all the other paths were valid. Anyway, if you encounter such an error, do not hurry to repair the SQL Server. There might just be a simpler solution.