How to set PreAllocate parameter in SQL Server Analysis Services and something more

I suppose you think that changing a parameter in SQL Server Analysis Services engine is quite easy stuff. And probably you are right, because I also thought so before I had to face some reconfiguration request. So straight to the point – changing this parameter is possible ONLY by manually editing the msmdsrv.ini file (the SSAS configuration file in the Config folder). After opening the file for editing just browse to the <Memory> section and you will find it on the bottom of it:


If you set a value between 0 and 100 then this will be converted to percentage of the Total Physical Memory that SSAS is configured to use. If the number is higher than 100 then it will be treated as absolute value in bytes. This setting is actually serving two purposes – warming up the SSAS memory and tacking an issue with memory allocation for SSAS on Windows Server 2003 (several reference articles can be found here and here).

So far so good. But this setting was quite new to me so before reaching the point of changing it I had to go through several different articles and whitepapers. Such are:

SQL Server 2008 White Paper: Analysis Services Performance Guide

SQL Server 2005 Analysis Services (SSAS) Server Properties

Based on the entire task for setting this and some other parameters I learned quite a few things for SSAS configuration. Some of them are:

1. There are two types of settings on SSAS instance level – basic and advanced. Advanced, just as the ones in SQL Server Engine, are hidden by default. In order to display and edit them you should check the box “Show Advanced (All) Properties” on the Analysis Services Properties window.


2. You can set absolute values for memory parameters – values between 0 and 100 are treated as percentage of the total physical memory available to SSAS (not the server itself). If the value is greater than 100 – then it is treated as an absolute value in bytes.

SQL Server 2008 SP3 available for download

It is been a long time since my last post and my list for blog article candidates constantly keep growing Smile I hope soon I will have enough time to return back to normal blogging speed.

Now I would like to share the new Service pack release for Microsoft SQL Server 2008 – list of all fixes can be found here. Actually as far as I could see those changes actually include all fixes till CU4 of SP2. So if you already have CU5 in place then applying SP3 is not really of a use. The SQL Server build version of SQL Server 2008 SP3 is 10.0.5500 (neat one, right? Smile)

Download SQL Server 2008 SP3 RTM

And happy patching Open-mouthed smile

What is new in SQL Server 2008 R2 Service Pack 1

I just patched my dev instance with Service Pack 1 for SQL Server 2008 R2 and in this post I will share some first impressions and facts about it.

After you apply the patch the first thing that comes to your attention is the version of the SQL Server. The new version of R2 with SP1 is 10.50.2500. I have to say I like that number Smile

Next thing taken out from BOL and the SP1 release notes is the modified DMV sys.dm_exec_query_stats. In SP1 we have four new columns introduced: total_rows, last_rows, min_rows, max_rows. Those columns provide information on returned rows statistics of a plan since its been last compiled.

There are couple of new interesting DMVs which might come handy from time to time:

sys.dm_os_windows_info –> returns information on Windwos release, service pack level and language

sys.dm_os_volume_stats –> this is a new DMF which returns information on the partitions SQL databases are hosted on. The valuable info here is the new possibility to check the free space on that partition.

   vs.volume_mount_point, -- e.g. C:\
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   sys.sysaltfiles AS f
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
   f.dbid < 32767
   drive_percent_free DESC,

*The above script is taken from the original post of Aaron Bertrand without any change whatsoever

sys.dm_server_registry –> this one is AMAZING. It has been a nightmare if you want to check any SQL Server related registry setting. Now you are able to check many of them just using this simple DMV

sys.dm_server_memory_dumps –> this one is keeping info on SQL Server dumps created during a crash so you can find easily the relevant info on the dump such as filename, when it was created and so on. At least now you do not have to browse to the error log folder and browse through all the files there looking for the one created around the crash you’ve been investigating.

sys.dm_server_services –> another just marvelous new DMV! With this one you can check all running services on the given server, their startup type, startup account, startup time and so on. Just keep in mind that this DMV just lists SQL Server and SQL Server Agent services.

sys.dm_os_sys_info –> this one is not new but has two new very important columns concerning virtualization – virtual_machine_type and virtual_machine_type_desc.

The rest of the stuff is for Extended Events and so far this is not my strong side so I will not just copy/paste the new stuff here trying to impress you Smile

Having said that I already have some great improvements in scripts I am regularly using in reports or ad-hoc information collection. The bad thing is that I still cannot lay hands on many (or more than one actually) SQL Server 2008 R2 SP1. I guess that is soon to be changed!

SQL Server 2008 R2 Service Pack 1 Released for download

Following the release news around SQL Server it seems that this week is quite busy with the CTP3 of SQL Server Code Name “Denali” and now the brand new Service Pack for SQL Server 2008 R2.

You can download it here.

So what is new in SP1? Well I did not have time to install it (still downloading Smile ) but according to some articles there is going to be a change in the DMVs we are so used to and keen on. One of them would be “sys.dm_exec_query_stats DMV which is extended with additional columns to improve supportabilities over troubleshooting long-running queries”. Of course there is and quite a long list with fixed bugs but I will leave to you to check them out here.

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!

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.


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
, 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 ( =  and ct.default_value != st.run_value)
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