SQL Server Life Cycle Support

 

Intro

My Director has been behind for a while here.

The imperatives are:

  1. Prepare Inventory list of all SQL Server Installs; the server name and current Version Number
  2. Apply latest Service Packs to all SQL Server Instances that are out of compliance
  3. Chart Upgrade path for the next few years to ensure that we narrow the list of versions that we are supporting

 

SQL Server Life Cycle Support

Stated below are the “Release to Manufacturing” (RTM) and Last Service Pack dates for the various versions of SQL Server.

We also noted the End of Mainstream and End of Extended Support where one has been officially announced by Microsoft.

 

Version Service Pack Release Date End of Mainstream End of Extended
SQL 2016 SP1 RTM :-  2016-June-1st
SP1   :-   2016-Nov-16th
 SQL 2014  SP2 RTM :- 2014-April-1st
SP2   :- 2016-July-11th
 SQL 2012  Sp3 RTM :- 2012-Mar-6th
SP3 :- 2015-Nov-23rd
 SQL 2008/R2  SP3 RTM :- 2010-April-21st
SP3 :- 2014-Sept-26th
 July 8th, 2014  July 9th, 2019
 SQL 2008  SP4 RTM :- 2008-April-7th
SP4 :- 2014-Sept-30th
 Jan 14th, 2014  Jan 8th, 2019
 SQL 2005  Sp4 RTM :- 2005-Nov-7th
SP4 :- 2010-Dec-17th
 April 12th, 2011  April 12th, 2016
 SQL 2000  SP4 RTM :- 2000-Nov
SP4 :- 2005-May-6th
 April 2008  April 9th, 2013

 

 

Suggestion

  1. v2005, v2000
    • Upgrade all current installs to 2014 and v2016
  2. v2008 & v2008/R2
    • Identify instances running v2008 and v2008/R2
    • Consult with clients and corresponding vendors
    • Plan and target upgrade for 2nd and 3rd quarter 2017 and First half of 2018

Current Landscape

  1. We do not have any SQL Instances running v2000, v2005, v2008
  2. We have 5 instances running v2008-R2

 

References

Microsoft – SQL Server Release Services

  1. 2008 and 2008-R2
    • End of Mainstream support for SQL Server 2008 and SQL Server 2008 R2
      Link
  2. 2000
    • End of Extended Lifecycle Support for SQL Server 2000 Service Pack 4
      Link

 

Products & Service Pack

SQL Server 2014

  1. SQL Server 2014 – RTM
    Link
  2. Server & Tools Blogs > Data Platform Blogs > SQL Server Release Services
    SQL Server 2014 Service Pack 2 is now Available !!!
    Link

 

Toad World – Release Date Calendar

  1. SQL Server 2008 Release Date Calendar
    Link
  2. SQL Server 2005 Release Date Calendar
    Link
  3. SQL Server 2000 Release Date Calendar
    Link

Microsoft Build Versions

  1. All
    1. SQL Server and Updates Builds Numbers
      Link
  2.  v2016
    • SQL Server 2016 build versions
      Link
  3. v2014

Service Pack List

  1. Microsoft SQL Server Version List
    https://sqlserverbuilds.blogspot.com/

Microsoft – SQL Server – Query Comparison Using SQL Profiler

Prelude

In a previous post we touched on the fact that we can use Quest Software’s Benchmark Factory and SQL Sentry’s Plan Explorer ( Link ) to compare competing queries and query plans.

 

Background

Once again to get a generalized database, we downloaded SQL Server 2014 AdventureWorks from here and restored it.

 

SQL Objects

 

Once our AdventureWorks database is available, we created a  couple of Stored Procedures that we can use to retrieve data from it.

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_1

 

 


use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_1') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_1 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_1
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin

    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]


    from   [Person].[Person] tblPerson

    where
                (
                       (@Firstname is null)
                    or (@Firstname = '')
                    or (Firstname = @Firstname)
                )
            and
                (
                       (@Lastname is null)
                    or (@Lastname = '')
                    or (Lastname = @Lastname)
                )


end
go

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2

 

Here is the second Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2


if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null
begin
 
    exec('create procedure dbo.usp_PersonFetchFilteredOnName_2 as select 1/0 as [shell]')
end
go
 
alter procedure dbo.usp_PersonFetchFilteredOnName_2
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin
 
 
    set nocount on;
 
    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]
 
    from   [Person].[Person]
    where
            (
 
                    (
                        Firstname = 
                                    case
                                        when (@Firstname is null) then Firstname
                                        when (@Firstname = '') then Firstname
                                        else @Firstname
                                    end
                    )
 
                and
 
                    (
                        Lastname = 
                                    case
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_3

 

Here is the third Stored Procedure ( dbo.usp_PersonFetchFilteredOnName_3 )

 

 


if object_id('dbo.usp_PersonFetchFilteredOnName_3') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_3 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_3
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]

    from   [Person].[Person]

    where
            (

                    (
                        Firstname = 
                                    case @Firstname
                                        when null then Firstname
                                        when '' then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case @LastName
                                        when null then Lastname
                                        when '' then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

SQL Management Studio

 

Here is the Query for invoking the Stored Procedures; along with the “Statistics IO” & “Statistics Time” measured.

 

Invoke SP

 

Here is the queries for invoking the SP.

 

 



exec dbo.usp_PersonFetchFilteredOnName_1
        @Firstname = 'Laura'
      , @Lastname  = 'Norman'


exec dbo.usp_PersonFetchFilteredOnName_2
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

    
exec dbo.usp_PersonFetchFilteredOnName_3
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

 

Statistics IO

 

StatisticsIO

 

 

Statistics Time

 

SQLServerTime

 

 

 

 

 

 

SQL Profiler

 

Here are the events we focused on using SQL Profiler.

 

Events Selection

 

Events Selection: Image

EventsSelection

 

Events Selection: Tabulate

 

Item Events & Columns  Item
Events & Columns
Stored Procedures
RPC:Completed
RPC:Starting
SP:Recompile
SP:Starting
 SP:StmtCompleted
Columns
TextData
CPU
Reads
Writes
SPID

 

 

 

 

 

Output

 

Output: Image

 

Here is the SQL Profiler Image.

SQLProfiler

 

Output:Tabulate

 

Metric SP-1 SP-2 SP-3
CPU 0 16  15
Reads 124 136  136
Writes 0 0  0
Duration 217 255  288

 

 

From the Screen-shot above, we can see that the Stored Procedures that use the “Case Clause” are a weeny bit more expensive.

 

Summary

 

Found that SQL Server Profiler when properly configured offers a slightly more concise view of a query’s performance data compared to SQL Server “Set statistics time on”.

 

 

SQL Server – Security – Permissions

Introduction

A big part of one’s life as a DBA is to understand a system’s security apparatus. In some cases, you want to grant very granular permission sets.

Granularity Requirement Permission Version
 Instance Run SQL Server Profiler\Capture SQL Server Trace grant alter trace to [Username]; v2005
 Instance Restore Database grant create database to [Username];
v2005
 Database Capture SQL Server Statement Showplan grant showplan to [database_principal]; v2005
 Instance View Server State grant view server state to [Login]; v2005
 Instance Alter Trace grant alter trace to [Login]; v2005
 Instance View Any definition grant VIEW ANY DEFINITION to [login]; v2005
 Database  View definition grant VIEW DEFINITION to [login]; v2005
Database – msdb Create and Schedule Jobs exec msdb.dbo.sp_addrolemember       N’SQLAgentUserRole’
    , [login-name];
v2005
Database – msdb  Review Job exec msdb.dbo.sp_addrolemember       N’SQLAgentReaderRole’
, [login-name];
v2005
Database – msdb Create new jobs, Review existing jobs, Start and Stop Jobs execmsdb.dbo.sp_addrolemember             N’SQLAgentOperatorRole’
, [login-name]
;
v2005
Database – msdb Review DB Jobs execmsdb.dbo.sp_addrolememberN’TargetServersRole’, [login-name]
;
v2000
 Instance Read error logMsg 229, Level 14, State 5, Procedure xp_readerrorlog, Line 1The EXECUTE permission was denied on the object ‘xp_readerrorlog’, database ‘mssqlsystemresource’, schema ‘sys’. GRANT EXECUTE ON master.dbo.xp_readerrorlog TO [login-name] v2005
 Instance SSIS Packages stored in msdbEnumerate all packages, View all packages, Execute all packages, Export all packages. exec msdb.dbo.sp_addrolemember N’db_ssisadmin’, [login];
 Instance SSIS Packages stored in msdbEnumerate own packages, Enumerate all packages, View own packages, Execute own packages, Export own packages exec msdb.dbo.sp_addrolemember N’db_ssisltduser’, [login];
 Instance SSIS Packages stored in msdbEnumerate all packages, View all packages, Execute all packages, Export all packages exec msdb.dbo.sp_addrolemember N’db_ssisoperator’, [login];
 Instance DBCC TraceOn/DBB TraceOff sysadmin
It does not appear that permission can be delegated
 proxy  Grant permission to use proxy Account  msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’%s’, @login_name=N’%s’
 Table  Truncate Table grant alter on [schema].[object] to [login-name]

Code – Syntax & Sample

 

Restore Database

Syntax:


use [master]
go

grant create database to [DOMAIN-NAME\AD-ACCOUNT];
go

 

Truncate Table

Syntax:


grant alter [schema].[object] to [login-name];
go

Sample:


grant alter on [dbo].[employee] to [hruser];

 

TargetServersRole

Please keep in mind that TargetServersRole only has this functionality in MS SQL Server v2000.

Syntax:

use [msdb]
go

exec sp_adduser N'<DOMAIN-NAME\AD-ACCOUNT>', N'<DOMAIN-NAME\AD-ACCOUNT>'
exec sp_addrolemember N'TargetServersRole', N'<DOMAIN-NAME>\AD-ACCOUNT'
GO

Sample:


use [msdb]
go

exec sp_adduser N'LABDOMAIN\dadeniji', N'LABDOMAIN\dadeniji'
exec sp_addrolemember N'TargetServersRole', N'LANDOMAIN\dadeniji'
GO

Database Engine – DBCC Permissions

DBCC Permissions

Granularity Requirement Permission Version
 Instance DBCC INPUTBUFFER Sysadmin for all sessions
Each user also has permissions to view text in his/her own sessions
N/A

Permissions – DDL – Stored Procedures

To be able to view Stored Procedures, you need to have “View Definition” permissions.Without it, you will have an empty “Stored Procedures” branch.ViewAnyDefinition Once granted, the Stored Procedures in the DB, will be listed:ViewAnyDefinitionGranted

Access to Proxy Account

Determine if Account has access to proxy

Sample:


use [master]
go

exec msdb.dbo.sp_enum_login_for_proxy
          @name = @account
	, @proxy_name = @proxy

Grant login access to proxy

Sample:


use [master]
go

msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'proxySQL', @login_name=N'LAB\svcControlM' 

Database Engine – Permission Lister

The other part of the permission puzzle is how to tell who has which permission. And, for that I offer you a nice blog post by Microsoft’s Rick Byham:

Effective Database Engine Permissions
http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx?wa=wsignin1.0&CommentPosted=true#commentmessage

Integration Services

http://technet.microsoft.com/en-us/library/hh213130.aspx
In previous versions of SQL Server, by default when you installed SQL Server all users in the Users group had access to the Integration Services service. When you install the current release of SQL Server, users do not have access to the Integration Services service. The service is secure by default. After SQL Server is installed, the administrator must grant access to the service.To grant access to the Integration Services service

  • Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
  • In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
  • Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
  • On the Security tab, click Edit in the Launch and Activation Permissions area.
  • Add users and assign appropriate permissions, and then click Ok.
  • Repeat steps 4 – 5 for Access Permissions.
  • Restart SQL Server Management Studio.
  • Restart the Integration Services Service.

On MS Vista  and later OSes, if the permissions listed above are not granted one will get the error message pasted below:

Connecting to the Integration Services service on the computer “Server” failed with the following error: “Access is denied.”

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service.

References

References – Truncate Table

  1. GRANT specific role ALTER access to specific table
    Link

 

References – Trace / SQL Profiler

References – ErrorLog

References – ErrorLog – Version

References – ShowPlan

References – Server

References – Definition

References – DBCC

References – DBCC / InputBuffer

References – msdb/packages

References – SQL Server Agent – TargetServersRole

References – SQL Server Agent

References – Integration Services

Technical: Microsoft – SQL Server – Analysis Services (v2012) – Deployment – Error – 08001 – No Such host is known

Introduction

While deploying a SSAS Solution, I ran into a bit hard to identify\trace error.

Errors

SQL Server Aliased Connection

Error 4 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A 
network-related or instance-specific error has occurred while establishing a 
connection to SQL Server. Server is not found or not accessible. Check if instance 
name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; TCP Provider: No such host is known. ; 08001. 
0 0 
Error	5	
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IISLogDW', Name of 'IISLogDW'.
0	0

Resolution – Changed Solution Data Sources to use actual server name.

I had created an Alias Connection by running cliconfg.exe and entering DB Connection Details:

SQLServerClientNativeNetworkUtility-IISLogDB

It seems that SQL Server Analysis Service (SSAS) does not consider SQL Server Alias Configurations.

As so we changed Solution Data Sources from using SQL Server Aliased connection to use actual server name:

DataSourceDesigner

Login failed for user

The other error we got is pasted below:

Error 4 OLE DB error: OLE DB or ODBC error: Login failed for user 'NT SERVICE\MSOLAP$MSSQL2012'.; 28000; Cannot open database "IISLogDW" requested by the login. The login failed.; 42000. 
0 0


Error 5 
Errors in the high-level relational engine. A connection could not be made to the 
data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. 
0 0

Resolution – Grant SQL Server Permission

And, it was far easier to debug and address.

--create login 
create login [NT SERVICE\MSOLAP$MSSQL2012]
from windows;

--use [db-name]
use [IISLogDW]
go

--create db user
create user [NT SERVICE\MSOLAP$MSSQL2012]
	   from login [NT SERVICE\MSOLAP$MSSQL2012]
go

-grant object level read permission
grant select on [dbo].[DimTime] to [NT SERVICE\MSOLAP$MSSQL2012];
grant select on [dbo].[factSales] to [NT SERVICE\MSOLAP$MSSQL2012];

Same Error

Same Error  (in MS Event Viewer) – OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; – Could not open a connection to SQL Server [53]. ; 08001.


OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related 
or instance-specific error has occurred while establishing a connection to SQL 
Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQLServerBooks Online.; 08001; Named Pipes Provider: Could not open a connection to SQL 
Server [53]. ; 08001.

Same Errors (in SQL Server Profiler \ Profiling SSAS) – OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; – Could not open a connection to SQL Server [53]. ; 08001.



Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while 
establishing a connection to SQL Server. Server is not found or not accessible. Checkif instance name is correct and if SQL Server is configured to allow remote 
connections. For more information see SQL Server Books Online.; 08001; Named Pipes 
Provider: Could not open a connection to SQL Server [53]. ; 08001. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Dim Time' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Calendar Year' attribute of the 'Dim Time' dimension from the 'salesDimensionDW' database was being processed. Server: The current operation was cancelled because 
another operation in the transaction failed. Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. OLE DB 
error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 08001. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Dim Time' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Full Date Alternate Key' attribute of the 'Dim Time' 
dimension from the 'salesDetectionDW' database was being processed.

Summary

Though Business Intelligent Development (BIDS) and SQL Server Data Tools support SQL Server Named Aliases, it seems SQL Server Analysis Services does not support SQL Server Named Aliases.

 

 

References

Transact SQL – Warning – No Join Predicate – Sourced to SQL as the ultimate free text scratch pad

Introduce

We all like SQL as it is fully expressive and lets us quickly get something down and add to it as we know more.

Sample SQL

Sample SQL – Good

Let us say we need to list object, column, and column Type data.  And, write a quick join on sys.objects, sys.columns, and sys.types:


select top 5 
	     tblObject.name
	   , tblColumn.name
	   , tblColumnType.name

from   sys.objects tblObject

	   inner join sys.columns tblColumn

		on tblObject.object_id = tblColumn.object_id

	  inner join sys.types tblColumnType

		on tblColumn.user_type_id = tblColumnType.user_type_id

where   tblObject.type = 'U'

Query Plan for Good SQL

sqlFreeText-Good

Sample SQL – Bad SQL

Drinking \ Glory days SQL.


select top 5 
	          tblObject.name
		, tblColumn.name
		, tblColumnType.name

from   sys.objects tblObject

	  inner join sys.columns tblColumn

		on tblObject.object_id = tblColumn.object_id

	inner join sys.types tblColumnType

		on tblObject.object_id = tblColumn.object_id

where   tblObject.type = 'U'

Query Plan for “Drinki(i)ng \ Glory Days” SQL

sqlFreeText-Bad

Quick Explanation:

If you look at the SQL, you will see that that the join clause on sys.types does not even reference the table name;  sys.types in this case:


inner join sys.types tblColumnType

		on tblObject.object_id = tblColumn.object_id

The Join on operators was a rush job and it was just a repetition of the preceding join clause.

Differences in Query Plan

Good SQL

In “Good SQL”, the “Nested Loops” operator has the “Outer References” operator that tells us which object is serving as the Outer element.

sqlFreeText-Good-Operator-NestedLoop-OuterReferences

Bad SQL

In “Bad SQL”, the “Nested Loops” operator does not have the “Outer References” element.

sqlFreeText-Bad-Operator-NestedLoop-OuterReferences

That is all!

Microsoft – SQLServer – High CPU – SPID – 1

The last few days has being interesting for this particular MS SQL Server Instance.

One weekend afternoon I saw it so busy.  As a DBA sometimes you learn a bit more from under-provisioned machines.  I hope I can learn a bit from this one.

Here is what I found so far:

  • Take it off the Internet.  It was being attacked and their were numerous entries of failed logins (sa – username no less)
  • Configured Symantec AntiVirus to exclude checking Network and MS SQL Server files; files with “known” SQL Server Extensions (mdf, ndf, ldf)
  • Configured Symantec AV to exclude C:\WINDOWS\system32\NavLogon.dll (http://www.symantec.com/connect/forums/rtvscanexe-high-memory-usage-sav)

Though these changes are in place and server restarted, some simple queries were still timing out and SQL Instance was still registering high CPU even when no queries were being processed.

From experience I know that Full-Text Search sometimes works in the background, but that did not appear to be the case at this time.

So ran a simple query looking for which Session was using high CPU.

SELECT tblSysProcess.cpu as processor, *
from master.dbo.sysprocesses tblSysProcess
order by tblSysProcess.cpu desc

And, here is what we got back:

So after a couple of days running this same query, SPID 1 stayed on top of our list of top CPU users.

Took the easy way and Googled for “SQL Server High CPU SP 1” and ended up where it made sense.

High CPU Usage for SPID #1 (SQL Server 2008)

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/bd9e4476-39e2-46f8-a426-bef9601f9ad9

In this particular post, Leila (posting as Amin S) did all the background work and asked the same question I was going to ask:

Hi all,

I have SQL Server 2008 (SP1) on Windows Server 2003 (R2).

Sometimes this server becomes very slow and task manager indicates high CPU usage. I added all SQL Server related threads in Performance Monitor to monitor Processor Time% per Thread.

By correlating Thread ID and KPID, I realized that this abnormal increase for CPU usage is always related to SPID #1 which is system process thus cannot be killed. After restarting SQL Server, everything works fine but after few minutes, exactly the same situation happens and SPID #1 consumes almost whole capacity of processors. I tried different ways to determine the source of problem. First I took our main database (financial) to offline state, and turned it back online. All users for this database were killed and SPID #1 released the processor! Sometimes this solution does not work and I use a loop to kill all users in every database. This works in most of times, but today I encountered situation that even killing all users did not work and I forced to restart the SQL Server engine several times during the day.

I was wondering if somebody could suggest a test for further observation or any solution for this issue.

Many thanks in advance,

Leila

Like I said, Leila did all the work.  A day later (s)he came back and answered his\her own question.

Leilia found a MS Link:

FIX: The CPU usage of the resource monitor is very high when the virtual memory is running low in SQL Server 2008

http://support.microsoft.com/kb/968722/en-us

And so it seems the next thing to do is determine the OS Thread ID for the “Resource Monitor”. This query will do so:

SELECT 
		  STasks.session_id
		, SThreads.os_thread_id
		, b.command 
FROM sys.dm_os_tasks AS STasks
	INNER JOIN sys.dm_os_threads AS SThreads 
		ON STasks.worker_address = SThreads.worker_address
	LEFT OUTER JOIN sys.dm_exec_requests b 
		ON STasks.session_id = b.session_id
WHERE STasks.session_id IS NOT NULL 
and command = 'RESOURCE MONITOR' 
ORDER BY sthreads.os_thread_id

I tried using “Performance Monitor” \ Perfmon. But, really not able to filter by Thread ID.
Probably should have tried SysInternals \ ProcessMonitor; as it has ability to observe at Thread ID, as well.

Need to address Memory Issue.

But, I think I have enough comfort level, that nothing will be hurt by upgrading from MS SQL Server v2008 SP1 to SP2.

Off we go.

 

References: