Monthly Archives: September 2013

Find Top 5 executed queries ordered by execution count

Dynamic Management Views, Scripts September 21, 2013 1 Comment

I wrote this query that helps us find executed queries with most number of execution counts. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 SQLtxt.text AS 'SQL',
qstats.execution_count 
AS 'Execution Count',
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time,
GetDate()) AS 'Logical Writes Per Second',
qstats.total_physical_reads AS [Total Physical Reads],
qstats.total_worker_time/qstats.execution_count AS [Average WorkerTime],
qstats.total_worker_time AS 'Total Worker Time',
DATEDIFF(hour, qstats.creation_time,
GetDate()) AS 'TimeInCache in Hours'
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS SQLtxt
WHERE SQLtxt.dbid = db_id()
ORDER BY qstats.execution_count DESC
Related articles
Advertisement

SQL Server 2012 – How to enable the “Lock Pages in Memory” Option

Memory September 17, 2013 Leave a comment

In this article I will discuss the feature “lock pages in memory” and demonstrate how we can enable the “Lock pages in Memory” option for SQL Server 2012 on the OS. The OS decides which accounts can utilize a process to retain data in the RAM. This would not allow the system to page out data to virtual memory on the physical disk. In SQL Server 2012, the memory architecture has been simplified for the usage of locked pages across all editions including processor. In SQL 2012 we enable this option by allowing the account which runs the SQL Server service to have this right. The below illustration shows the requirements for different editions of SQL server for Locking pages in memory. LP1If you have migrated your SQL server to SQL 2012 from SQL 2005 or SQL 2008 and if you have been using this option then you can remove the trace flag 845 from the start up parameters.

We will now see how to enable this option.

        1. Click Start, click on Run. Type gpedit.msc. Click on OK and you will find the Local group policy editor opening.
          LP2         LP3
        2. On the Local Group Policy Editor, expand Computer Configuration -> expand windows Settings -> Security settings
          LP4 LP5
        3. Open Security Settings, and then Open Local Policies and then open “User Rights Assignment” folder
          LP6
        4. Once you open the User Rights Assignment folder all the policies will be displayed. Locate “Lock pages in Memory”. Double Click on “Lock pages in memory”
          LP7LP8
        5. Click on “Add user or Group” and add the account that runs SQL Server service. Restart SQL server.
          LP9
          We can verify whether SQL Server 2012 is using “locked pages” by the following ways:
          1) Run the following query:

select od.node_id, os.memory_node_id, od.node_state_desc, os.locked_page_allocations_kb
from sys.dm_os_memory_nodes os
inner join sys.dm_os_nodes od on
os.memory_node_id = od.memory_node_id
where od.node_state_desc <> 'ONLINE DAC'

The locked_page_allocations_Kb will display a non-zero value.

LP10              2) DBCC MEMORYSTATUS will show the following:
LP11 I hope this article was helpful in understanding how we can enable this option for SQL Server 2012.

Optimize for adhoc workloads – Server Configuration Option – How it works

Performance Optimisation September 12, 2013 Leave a comment

In this article I want to demonstrate with an example how this feature works when configured. let us begin by first understanding what is “Optimize for adhoc workloads” .

This feature helps improve the plan cache’s efficiency to handle workloads that contain many single use of ad hoc batches. When set to 1, the engine keeps a small compiled plan stub in the plan cache when an ad hoc  batch is compiled for the first time, instead of the full compiled plan. This helps in releasing memory pressure by not allowing the plan cache to become filled with compiled plans that will not be used again.

This compiled plan stub enables the SQL engine to decide that an ad hoc batch has been compiled previously but has only stored a compiled plan stub. So when this batch is compiled or executed again, the SQL engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache. This will only affect new plans. Plans that are already in the plan cache are unaffected.

let us understand this with an example
Step 1: Clear the Buffer cache and plan cache
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 2: Run an Ad hoc query
USE AdventureWorks2012
GO
SELECT * FROM Production.Document WHERE DocumentLevel = 2
GO
WL1Step 3: Find if the query is cached
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
 text like ‘%SELECT * FROM Production.Document%’
ORDER BY usecounts DESC;
GO
WL2Step 4: Enable the “Optimize for ad hoc workloads”
SP_CONFIGURE ‘show advanced options’,1
RECONFIGURE
GO
SP_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO

Step 5: Repeat Step 1
Step 6: Repeat Step 2
Step 7: Repeat Step 3
You will find that the query is not cached
WL3Step 8: Lets clear the cache and buffers again. Repeat step 1
Step 9: We need to run the ad hoc more than once now. Execute the below
SELECT * FROM Production.Document WHERE DocumentLevel = 2
GO 3
WL4Step 10: Now lets see if the query is cached
Step 11: Repeat step 3.
WL6 Yes we do see that when the same batch has multiple executions, the query is indeed cached.

To summarize the utility of this feature we can say that it helps SQL Server memory by not allowing to store Ad hoc query plans which are for single use only.

Create a report based on a parameter using SQL Server reporting services

SSRS September 11, 2013 Leave a comment

In this demo we will create a report based on a parameter using SQL Server Reporting services. In my previous post i had shown a demo as to how to create a basic report using SQL Server reporting services.

Step 1: Open SQL Server Report Builder
Step 2: Create a new Datasource as below
R23Step 3: Create 3 new datasets as below
R24R29R30Step 4 : Right click on Parameters option and click on “Add parameter”
R25Step 5: Create a new parameter as below
R26Step 6: Click on “Available Values” and set the options as depicted
R27Step 7: Click on “Advanced” option and set the options as depicted. Click OK.
R28Step 8: Click on Insert tab and select “Insert matrix”.
R31Step 9: Design the matrix as shown below
R32Step 10: Save the report. Now the report is ready to be run. Click on the run button in the home tab. The IE should show you the below. Select a country or multiple countries from the drop down and click on “View Report”.
R33R34I hope this demo would help you to create a report based on a parameter.

Related articles

Find Top 5 expensive Queries from a Write IO perspective

Dynamic Management Views, Scripts September 10, 2013 Comments: 2

Recently i wrote this query that helps us find the most expensive queries from a write IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 sqltxt.text AS ‘SQL’, qstats.total_logical_writes AS [Total Logical Writes],
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS ‘Logical Writes Per Second’,
qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time AS [Total Worker Time],
qstats.total_worker_time/qstats.execution_count AS [Average Worker Time],
qstats.total_physical_reads AS [Total Physical Reads],
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘TimeInCache in Hours’,
qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,
db_name(sqltxt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS sqltxt
WHERE sqltxt.dbid = db_id()
ORDER BY qstats.total_logical_writes DESC

Find Top 5 expensive Queries from a Read IO perspective

Dynamic Management Views, Scripts September 10, 2013 Leave a comment

The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 qt.text AS ‘SQL’, qstats.total_physical_reads AS ‘Total Physical Reads’, qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,    qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time/qstats.execution_count AS ‘Average Worker Time’,
qstats.total_worker_time AS ‘Total Worker Time’,
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘AgeInCache In Hours’,  db_name(qt.dbid) AS ‘Database Name’
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qstats.total_physical_reads DESC

How to create a report using SQL Server 2012 reporting services

SSRS September 9, 2013 Leave a comment

In this article i would like to give a demo as to how to create a report using SQL Server 2012 reporting services. I have been developing reports using this tool for some time and this attempt is to share my knowledge for the same. In this article i am assuming that SQL Server Reporting services is installed and is in a running state.

Step 1: Open the Report Manager Home page
R1Step 2: Click on the Report Builder tab.
R2
R3Step 3: Once the Report Builder opens, you will see the following screen -> Click on “New Report” -> “Blank Report”
R4Step 4: Once click on “Blank Report” you will find the below scree. This is the report builder where we will develop our first report. The while blank area is the body of the report where we will insert a matrix. On the left hand panel are the data properties based on which the report will function. Let us go through each of the terms that we see there.
a) Built-in-Fields: Built in fields help us render in built functionality on our report page. For example if we want page number on each of our pages in a report we can use the “Page Number” field from here. We will be using them in our demo.

b) Parameters : Parameters help us to build reports based on a certain input. For e.g if we want to extract some information based on 2 dates, then we need to define parameters based on which the report will pull data and display.

c) Images: This will help us add images to the report.

d) Data Sources: very important aspect of Report Builder(RB). Data source help us define and maintain the source from which we will pull data for the report. I will explain Data source with more clarity once we start developing the report.

e) Datasets: A dataset specifies a query, query parameters, filters, and a field collection. It might be a query, table or stored procedure.
R5Let start to build our report now.

Step 5: Right click on Data Sources -> Add Data Source
R6Step 6: You will see the below screen once you click on Data Source properties. Choose the below depicted options and click on the build button
R7Step 7: Once you click on the build button, the “Connection properties” dialog box will come up. In the server name field, type in the name of the SQL server from where you want to pull data. Once you feed the server name, select the name of the database from where you want to pull data. In our example we have used the [AdventureWorksDW_WroxSSRS2012] database. Click on “Test Connection” button to verify the connection. Click on OK.
R8R9Step 8: On you click on OK you will see the “Data Source Properties” window with the connection string embedded. Click on OK. Now your data source is ready
R10R11Step 9: Now we need to create a new dataset for our report to access.Right Click on “Datasets” -> The Data set properties dialog box pops up -> Under the name type in a name of the dataset. Choose “Use a dataset embedded in my report” option and choose Datasource1 from the dropdown. Select query type as “text” and paste the below query in the text box
SELECT  vResellerSalesProdTerrDate.CalendarYear  ,vResellerSalesProdTerrDate.CalendarQuarter
  ,vResellerSalesProdTerrDate.MonthNumberOfYear  ,vResellerSalesProdTerrDate.MonthName
  ,vResellerSalesProdTerrDate.Category  ,vResellerSalesProdTerrDate.Subcategory
  ,vResellerSalesProdTerrDate.ProductName  ,vResellerSalesProdTerrDate.SalesTerritoryGroup
  ,vResellerSalesProdTerrDate.SalesTerritoryRegion  ,vResellerSalesProdTerrDate.SalesTerritoryCountry
  ,vResellerSalesProdTerrDate.SalesAmt  ,vResellerSalesProdTerrDate.OrderQty
  ,vResellerSalesProdTerrDate.Frieght FROM
  AdventureWorksDW_WroxSSRS2012.dbo.vResellerSalesProdTerrDate

Click on the fields tab below the query tab and you find the fields populated. Click on Ok and you will find your dataset ready. Below images for illustration.
R13R14R15
Step 10: Now Click on the “Insert” tab -> Matrix Wizard
R16Step 11: Once you click on the Matrix Wizard you will find the dataset that you had created few steps back selected. Click on that dataset, click on next
R17Step 12 : Drag and drop the fields as shown in the below illustration. Click Next
R18R19Step 13: Select the Corporate Style in the Styles Tab -> Click Finish
R20Step 14: When you click on finish you will find a template which will have the fields you had selected.Give a name to your report.  Expand the Built-in Fields option and drag the “Page Number” property below the template. Save the report.
R21Now your report is ready. Click on the Run button and your report will open up in IE.
R22I hope this article was helpful in building a basic report from scratch. In my next post i will discuss how to build a report based on parameters.

SQL Server 2008 -How to troubleshoot error: Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856

Troubleshooting September 7, 2013 Comments: 2

A few days ago i encountered the below mentioned error message in SQL error logs in one of our production environments
Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856.

I found the following KB Article on MSDN to throw light on this issue. As per the article there was a stack dump that we found in the server.
StackWhat caused the schedulers to be non-responsive:
As per the article the cause of the issue is:
“This issue occurs because the profiling session is not closed correctly. The profiling session is used to calculate the cost of an object to be cached, and is based on memory usage and CPU usage”.

Resolution as Provided by MS in the Kb article for SQL server 2008 is:
Apply CU6 for SP 3.

Our patch level in the environment is beyond that and still this issue happened, so literally there is no solution provided by MS. We would wait and see if this error comes back too often and open a case with MS support.

SCOM -TSQL Script – Find active alerts in a SCOM managed environment

Operations Manager, Scripts September 7, 2013 1 Comment

I had to write this query as SCOM dashboard doesn’t always give depict a proper view of the open active alerts in the system. This would be helpful for creating reports for SCOM or for monitoring purposes as to which alerts are open and from which management packs.

DROP TABLE #Base_State
Go
SELECT *
INTO #Base_State
from
(
SELECT aa.Id AS [AlertId],aa.TimeRaised AS [RaisedDateTime],
aa.Severity,aa.ResolutionState,
CASE WHEN aa.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS Status
,mp.MPName AS [ManagementPack],
mp.ManagementPackId AS [MPID]
 FROM
OperationsManager.
dbo.AlertView (NOLOCK) aa
 LEFT JOIN
 OperationsManager.dbo.RuleView (NOLOCK) Rv
ON aa.MonitoringRuleId = rv.Id
JOIN OperationsManager.dbo.ManagementPack mp
ON rv.ManagementPackId = mp.ManagementPackId
WHERE aa.Severity = 2 AND
 aa.ResolutionState < 254
union
SELECT aa.Id AS [AlertId],aa.TimeRaised AS [RaisedDateTime],aa.Severity,aa.ResolutionState,
CASE WHEN aa.ResolutionState < 254
 THEN 'Open' ELSE 'closed' END AS STATUS,
mp.MPName AS [ManagementPack],
mp.ManagementPackId AS [MPID]
FROM
OperationsManager.dbo.AlertView (NOLOCK) aa JOIN OperationsManager.dbo.MonitorView (NOLOCK) Mv
ON aa.MonitoringRuleId = Mv.Id
 JOIN OperationsManager.dbo.ManagementPack mp
ON mv.ManagementPackId = mp.ManagementPackId
WHERE aa.Severity = 2 AND
 aa.ResolutionState < 254
UNION
SELECT aa.AlertGuid AS [AlertId],aa.RaisedDateTime,
aa.Severity,ars.ResolutionState,
CASE WHEN ars.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS STATUS,
mp.ManagementPackSystemName AS [ManagementPack],
mp.ManagementPackVersionIndependentGuid AS [MPID]
FROM OperationsManagerDW.Alert.vAlert (NOLOCK) AA JOIN OperationsManagerDW.Alert.vAlertresolutionstate (NOLOCK) ARS
ON aa.AlertGuid = ARS.AlertGuid JOIN
OperationsManagerDW.[dbo].vRule (NOLOCK) Vr
ON aa.AlertProblemGuid =  Vr.RuleGuid JOIN OperationsManagerDW.dbo.vManagementPack (NOLOCK) Mp
ON vr.ManagementPackRowId =  mp.ManagementPackRowId
WHERE aa.Severity = 2 AND
ars.ResolutionState = 255
union
SELECT aa.AlertGuid AS [AlertId],aa.RaisedDateTime,
aa.Severity,
ars.ResolutionState,
CASE WHEN
 ars.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS STATUS,
mp.ManagementPackSystemName AS [ManagementPack],
mp.ManagementPackVersionIndependentGuid AS [MPID]
FROM OperationsManagerDW.Alert.vAlert (NOLOCK) AA JOIN OperationsManagerDW.Alert.vAlertresolutionstate (NOLOCK) ARS
ON aa.AlertGuid = ARS.AlertGuid JOIN
OperationsManagerDW.[dbo].vMonitor (NOLOCK) Vm
ON aa.AlertProblemGuid =  Vm.MonitorGuid JOIN OperationsManagerDW.dbo.vManagementPack (NOLOCK) Mp
ON Vm.ManagementPackRowId =  mp.ManagementPackRowId
WHERE aa.Severity = 2 AND
 ars.ResolutionState = 255
) AS Base

SELECT bs.AlertId,bs.RaisedDateTime,
bs.ResolutionState,bs.Status,
bs.ManagementPack
FROM #Base_State BS
WHERE Bs.Status = 'Open'

 

Scom

How to create a trace from sql server profiler

Trace September 6, 2013 Leave a comment

In this post i would like to demonstrate how to create and run a trace on a SQL Server.
Let us start by understanding what a trace does for us. It helps us gather events that are happening on the instance. These events gathered, help us analyze and understand the state of the SQL server or help us understand any specific issue that we are troubleshooting. Let us understand this with an example:

Step 1: Open SQL Server Management Studio(SSMS)
Step 2: Click on Tools -> Click on SQL Server Profiler
CT1Step 3: Once Profiler opens it will ask for the name of the SQL server on which you want to run the trace
CT2Step 4: In the next window we need to feed the profiler with what we want to capture such as events,template. We can also set a stop time for the trace to stop automatically. The general tab also allows to decide where we want to save the trace file i.e we can either save the trace file as a file or save the data as a table in a database. Apart from that the template drop down helps us decide what kind of data we would like to capture. In our example we will save the trace in a file. We also need to set the maximum file size and if a file would roll over in case the maximum limit is breached.
CT3Step 5: Now let us click on next tab “Events Selection”
CT4Step 6: Check the “Show all events” and “Show all columns” options. Scroll down to the “Stored procedures” events and check the below marked options. Then scroll down to the “TSQL” events and check the following options as below
CT5CT6Step 7: On the same tab we would see to buttons “Column Filters” and “Organize Columns”. Click on “Column Filters” and browse through the options it brings along. you might want to filter out any particular aspect of the system on which you want to collect data. In this example we would select the below
CT8Step 8: Click on OK and our trace is ready to run. Click on Run and you would see the trace capturing data
CT9Step 9 : To Stop the trace click on the stop button
CT11If we want we can save the definition of the trace as a T-SQL script. This is how we would achieve the same.
Step 1: Click on File -> Export -> Script Definition -> For SQL Server 2005 – 2008 R2
CT12Step 2: Save the SQL script in a desired Location
CT13In the next post i will demonstrate how to use this SQL script for later use for capturing data. I hope this demo was useful.

Related articles

How to read the content of one or more trace files

Trace September 6, 2013 Leave a comment

To read the content of one or ore trace files we need to use the following system provided function by SQL Server:

fn_trace_gettable ( ‘filename’ , number_files )
Where
‘filename’: is the initial trace file to be read. Filename is nvarchar(256), with no default.

number_files:Indicates the number of rollover files to be read. This number includes the initial file specified in filename.The value is an integer.

Let us see how it works with the help of an example:
Step 1: let us first find the traces running in the system
SELECT * FROM sys.traces
Go
Output gave us:
Trace1Step 2: Choose the trace that you want to open. In this case we have only one trace running. Copy the value from the path column and pass it on as a parameter in the below function

SELECT * FROM sys.fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0) WHERE TextData IS NOT NULL
Output gave us:
Trace2We can save the output in a temporary table if we want look further into the data:
Use Databasename;
GO
SELECT * INTO Temp_table
FROM fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0);
GO

Related articles

How to find if there is a trace running in the server

Scripts, Trace September 6, 2013 Leave a comment

What is Server-side tracing?
Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool.  Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

Here is how you view the number of traces currently in the system and the details of the trace:

Number of traces(active and stopped) currently in the system:
select * from sys.traces

Here is how you view the active(trace is running) traces in the system:

SELECT * FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

You can stop and terminate a trace by executing the following:

EXEC sp_trace_setstatus @traceid , @status = 0 — Stops the trace
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.

 

Related articles

SQL server 2008 – How to troubleshooting error: The fulltext filter daemon host (FDHost) process has stopped abnormally.

Full-Text Search, Troubleshooting September 5, 2013 Leave a comment

A few days ago i came across the below message in our SQL Server logs:
Error: 30089, Severity: 17, State: 1.
The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

Full-text search is a key component in our SQL environment as we support a database that consists of millions of documents. I started scanning the FT crawl log and also found the below error:
Error ‘0x80004005’ occurred during full-text index population for table or indexed view ‘[DBNAME].[dbo].[TABLENAMe]’ (table or indexed view ID ‘10045673’, database ID ‘6’), full-text key value ‘4’. Attempt will be made to reindex it.

In case you are not aware as to where to find the crwal log then go the following location:
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG folder- this is where log folder would be for SQL logs. Under this folder you will find files with following pattern:

SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]
Where DatabaseID is
The ID of a database. <dbid> is a five digit number with leading zeros.<FullTextCatalogID>
Full-text catalog ID. <catid> is a five digit number with leading zeros.
n an integer that indicates one or more crawl logs of the same full-text catalog exist.

For example, SQLFT00006000010.5 is the crawl log file for a database with database ID = 5, and full-text catalog ID = 10. The 5 at the end of the file name indicates that there are two crawl log files for this database/catalog pair.

I searched the msdn site with error message and this is what i found as the cause of the error:
This problem occurs because of a bug in the communication between the Sqlservr.exe process and the Fdhost.exe process. When batches are sent from the Sqlservr.exe process to the Fdhost.exe process, sometimes the outbound shared memory (OSM), which is the shared memory from Sqlservr.exe to Fdhost.exe, has insufficient space to fit the next column of a document. When this occurs, the Sqlservr.exe process sends a message to notify the Fdhost.exe process to process all the existing data in the OSM. While the Fdhost.exe process is processing data, the inbound shared memory (ISM), which is the shared memory from Fdhost.exe to Sqlservr.exe, may become full. In this situation, the Fdhost.exe process sends a “rewind” message to the Sqlservr.exe process. This message requests that the Sqlservr.exe process resend the last document from the first column. However, when the Sqlservr.exe process starts to send the document again, it starts from column 2 instead of column 1.

Resolution:
Apply Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2 and Cumulative update package 4 for SQL Server 2008

MS also suggests an workaround for the same which is increasing the ism size value:

What is ISM. Well ISM is the internal shared memory used by the FDHost.exe process.

To show the current value:
sp_fulltext_service ‘ism_size’

To change the value to a new value:
sp_fulltext_service ‘ism_size’,@value=NewValue

Useful tech articles of Full-text search:
http://msdn.microsoft.com/en-IN/library/ms142560%28v=sql.100%29
http://msdn.microsoft.com/en-us/library/cc721269%28SQL.100%29.aspx

Buffer cache hit ratio – How useful it is?

Memory September 4, 2013 1 Comment

Buffer cache hit ratio(BCHR) : Well every dba who has ever done any Performance tuning work would know about this particular counter. So what is BCHR: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

And how much guidance does it give us….

Well to know that i had to share this great article from . I read this article on http://www.simple-talk.com. Read the article and challenge your understanding of BCHR once again.

SQL Server 2008 – How to troubleshoot error : Granted or revoked privilege EXECUTE is not compatible with object.

Security, Troubleshooting September 4, 2013 Leave a comment

Today while executing the following code i got this particular error message:

GRANT EXECUTE ON dbo.fn_SomeFunction_TVF TO SomeRole

Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

I started to read about this error message and this is what i concluded.

You cannot grant execute permissions on Table-valued functions. This is the way this has been designed. Though i could not find as to why this is so.

Solution:
I had to use the references keyword. The syntax for the grant statement hence becomes:
GRANT REFERENCES ON dbo.fn_SomeFunction_TVF TO SomeRole.

A snapshot of the MSDN article that details out permission levels to different securables is below.

Grant

For more details please visit the msdn link :
http://msdn.microsoft.com/en-us/library/ms191291.aspx

%d bloggers like this: