Monthly Archives: September 2013

Find Top 5 executed queries ordered by execution count

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

Advertisements

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

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

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

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.

Find Top 5 expensive Queries from a Write IO perspective

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

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

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.

%d bloggers like this: