Some more info on message 17890 ‘A significant part of sql server process memory has been paged out’

Hi All, This message, which has error ID 17890  is a rather common troublemaker, as the number of web search hits against it will show. Example : A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7215 seconds. Working set (KB): 7901284, committed (KB): 17040596, memory…


Detecting Possible Parameter Sniffing Occurences with DMV

Hi All, Parameter Sniffing is a scenario which most DBAs had to handle at some point. Usually the main challenge is to detect it, as the toolkit to address it is well known and documented. To help in the detection phase, here’s a rather simple query that will just report queries which have a large…


SQL Server’s SPN autoregistration and ListenAll IP setting

Hi Everyone ! Today we’ll look at a the ListenAll settings of the SQL Server server-side protocol. When active, this setting will have SQL Server bind all available  IPs of the server, and use the same listening port for all of them. Disabling ListenAll allows to manually select which IPs the SQL Server instance will…


SetFileIoOverlappedRange messages in SQL Server Errorlogs

Hi Everyone, I had recently to understand why a SQL Server had repeated occurences of “SetFileIoOverlappedRange failed, GetLastError is 1314” in its errorlog. Because the web lookup of the main keyword would likely lead to http://support2.microsoft.com/kb/2679255/en-us, that (rightfully) would make the DBA worried about what could possibly be happening on the Server. Because Os error…


X-Ray of a SSAS client time-out and query cancel

Hi Everyone, In a previous post, we took a look at SQL Server’s implementation of a time-out and of a user cancel. Today we’ll look at SSAS and examine the same operations. SSAS Timeout. In Management Studio (SSMS), I change the execution timeout from the default 0 to 10 : I then launch a long…


X-ray of SQL Server timeout and user cancel

Hi Everyone, We’re going to take a close look at how SSAS and SQL Server implement execution timeout and user cancellation, and we’ll discover a few noticeable differences. Today we’ll check SQL Server. SQL Server Timeout For this test I’ve executed a simple “waitfor delay ’00:00:15′” that will … wait for 15 s. WIth an…


Advanced Dimension security in SSAS

Hi , Well first, for this new article, we’ll try English for a change, after this quick French foreword : Nous n’oublions pas les francophones, et sur simple demande dans les commentaires, je serai heureux de vous proposer une traduction en Français et nous répondrons bien sûr en Français aux commentaires en Français. I’ve recently…


SQL Server compilation Gateways and ‘RESOURCE_SEMAPHORE_QUERY_COMPILE’

    Hi everyone, In this post, we’ll spend some time exploring the SQL Server mechanism that controls the ongoing compilations and their memory usage, and understand its purpose and consequences on the incoming workload. Chances are, you ended-up here because of a search against ‘RESOURCE_SEMAPHORE_QUERY_COMPILE’ or ‘Gateway’ : ·         ‘RESOURCE_SEMAPHORE_QUERY_COMPILE’ will be a waittype…