Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate SQL Provision
The Voice of the DBA
 

Why A Query May be Faster the Second Time it Runs

Today we have a guest editorial from Kendra Little as Steve is away on his sabbatical.

A discussion came up on Twitter recently about database query performance: if you execute a query twice, it may be faster the second time it runs. 

This can happen for a variety of reasons, but often the answer lies in how the database engine takes advantage of memory. For example, SQL Server will cache the data used by the query in the buffer pool whenever it can. If the data is not forced from the buffer pool due to failover, memory pressure, or another action, subsequent executions of this query (or other queries which happen to use the same data) can reference the data directly from memory and skip a slow trip to read the data from disk. 

When testing this from an interactive query window against SQL Server, one of my longtime favorite commands is the following: 

SET STATISTICS TIME, IO ON;

GO 

After this statement is run, you will receive information about how many reads were done by the query in the following categories: 

  • Logical reads – the total number of reads done (these may or may not have read from disk) 
  • Physical reads – a physical read copying data from disk into the buffer pool cache. In Statistics IO output this number does not include read-ahead reads. 
  • Read-ahead reads – an optimized type of physical read (from disk).  

You can learn roughly how much your query took advantage of data stored in the buffer pool cache by doing a little math: logical reads – (physical reads + read-ahead reads) = roughly how much data already in memory was used. 

If you’d like to learn more about using the STATISTICS TIME and STATISTICS IO commands, I’ve written a post on some tricks you can use with these commands. 

Kendra Little

Join the debate, and respond to today's editorial on the forums

Redgate Database Devops
 
 Featured Contents
Stairway to Azure SQL Database

Creating a Logical SQL Server: Level 2 of the Stairway to Azure SQL Database

BLOB EATER from SQLServerCentral.com

In this second level of the Stairway to Azure SQL Database, we cover the basics of building a logical server using the portal and PowerShell.

Removing the Square Bracket Decorations with SQL Prompt

Additional Articles and Phil Factor from Redgate

If you avoid illegal characters and reserved words in your identifiers, you'll rarely need delimiters. Sadly, SSMS applies square bracket delimiters indiscriminately, as a precaution, when generating build scripts. Phil Factor provides a handy function that adds quoted delimiters only where they are really needed and then sits back and lets SQL Prompt strip out any extraneous square brackets, in a flash.

Running Database Console Commands (DBCCs) on Azure SQL Database

Additional Articles from Database Journal

Practically every SQL Server Database Administrator operating in an on-premises environment is familiar with Transact-SQL DBCC statements that serve the role of Database Console Commands. However, there are some additional considerations that should be considered when using them in the context of Azure SQL Database deployments.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Redgate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".

From the SQL Server Central Blogs - Microsoft Tools That Help Query Tuning

Grant Fritchey from The Scary DBA

Query tuning is not easy. In fact, for a lot of people, you shouldn’t even try. It’s much easier to buy more, bigger, better hardware. Yeah, the query is...

From the SQL Server Central Blogs - Common commands and tasks to make dealing with Windows Core easier.

Kenneth.Fisher from SQLStudies

I don’t know how many of you are working with Windows Core these days but personally I think it’s a ... Continue reading

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Read Only Files

I have this python code: 
with open("C:\Users\way0u\Documents\lotsofdata.txt", XXX) as data_file:
    # do stuff
What should I put in place of XXX to open this file as a read only file in text mode?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Creating a Node Table

I am creating a member table that I will use in a new application. I want to use the graph query structures to analyze data and need this to be a node table. What is the correct syntax for this?

-- 1
CREATE NODE TABLE Member (
MemberFirstName varchar(100),
MemberLastName varchar(100),
MemberTitle varchar(100)
) AS NODE

-- 2
CREATE TABLE Member (
MemberFirstName varchar(100),
MemberLastName varchar(100),
MemberTitle varchar(100)
) AS NODE

-- 3
CREATE TABLE Member (
MemberFirstName varchar(100),
MemberLastName varchar(100),
MemberTitle varchar(100)
) WITH (NODE=ON)

-- 4
CREATE TABLE Member (
MemberFirstName varchar(100),
MemberLastName varchar(100),
MemberTitle varchar(100)
) AS GRAPH NODE

Answer: 2

Explanation: The correct syntax is 3, which is a CREATE TABLE syntax with AS NODE at the end. Ref: CREATE TABLE - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-sql-graph?view=sql-server-ver15

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration
Error switch partition - hello Good morning all I want to switch the partition from one table to another I have this error message on a blocking because of the index   ALTER TABLE sales.SalesOrderDetail SWITCH PARTITION 1 TO sales.SalesOrderDetail_2014 PARTITION 1;   Warning: the partition specified 1 for the table 'produit.sales.SalesOrderDetail_2014' was ignored in the ALTER TABLE SWITCH […]
SQL Server 2017 - Development
BULK insert several thousand files - I have a bunch of logging data that my sysadmin has downloaded from our elastic load balancers at amazon and I need to stick it in a completely bland table for a one off sales pitch. I've scripted up all of the unzip, copy, etc etc…  but I've become so reliant on SSIS that I've […]
SQL Server 2016 - Administration
upgrade plan common sense check please - Be grateful for a yay or nay on my plan: 4 nodes. 2012.  1 x AG.  FCI.  1 local sync. 2 remote async. Upgrade to 2016 on all, failing over as required, doing remotes, then local secondary, then primary. Reboot all.  Run checks DBCCs etc. Then upgrade to SP1 via the same method above. Would […]
SQL Patching implementation date - Hi I am looking at the script to get SQL Patching implementation date . Any script that I can execute using SSMS ? Thanks Your feedback is much appreciated    
SQL Server 2016 - Development and T-SQL
Execute business rule runtime - Hi I have set of hundreds of business rules that I need to executed based on certain criteria. Just for example If product =A -          Code > 5   (code should be greater then 5) -          Height > 10 -          Width >5 If product = B -          Height > 8 -          Width >7 I want to […]
Trigger for only updating specific column - Hi, using Merge I can sync table A with Table B at ROW level. However 'how to update only specific column instead of the whole row'? Sincerely!  
Count of Distinct Clients in a list, in groups - Hello all, I have a dataset of trip information (the majority of which I got from another awesome forum member not long ago) where I need to be able to get a distinct count of passengers in the groups when someone is onboard the vehicle.  This trip information shows when a vehicle is picking up […]
What could cause MS Access app as a front-end to SQL, periodically run slow? - We've got an old Microsoft Access pharmacy application which acts as a front-end to a SQL Server 2016 database. Periodically the application will just start running slowly. We're working on a modern replacement app, but that won't be available for months. In the meantime, what can we do about this application suddenly running slowly? Here's […]
Development - SQL Server 2014
Recover data log loss - Hi. I would like to know is it possible to recover data log table from SQL Server 2014 without backup file?
SQL 2012 - General
What is spinlock ALLOC_CACHES_HASH? - Hi, Queries are slow, cpu usage at 80%. ALLOC_CACHES_HASH backoffs start at same time as problems are seen in application. "CACHES" would point to memory, but no obvious shortage there. This document says "internal use only"... https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-spinlock-stats-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-previousversions What to check next? spinlock_name delta_spins delta_backoff delta_minuntes delta_spins_per_millisecond_per_CPU ALLOC_CACHES_HASH 72412901394 16796814 30156 100053 SOS_OBJECT_STORE 17456 1 30156 […]
SQL Server 2019 - Administration
TDE / SQL server Database / SSIS - Dear Everyone We have implemented a data warehouse on-premises using SQL server 2019 enterprise edition running on VM Ware. SSIS, Database Warehouse and SSAS all reside on different servers in the same VLAN and now I am working on implementing TDE on the servers but I wanted to know the following: Is TDE required for […]
SQL Server Licencing Question - This question is about SQL Server Licencing and licencing for Maximum Virtualisation. I'm curious about the licence position for this scenario. You have one Hyper-V host (HOST1) with say 48 cores all core licenced with Enterprise Core and covered by SA. You can run an unlimited number of VMs. You have a second Hyper-V host […]
SQL Server 2008 - General
Error converting data type varchar to float - All, I know this topic has been discussed numerous times and I tried all possible solutions but to no avail, so here I am. I have an app that stores values like this in a varchar(7) column called Utilization in a table called CC_Agent_State_Summary : 0.02% 16.2% 99.91% The app does nothing but store and display […]
Reporting Services
SSRS 2016 install on FCI with AAAG. - Appreciate advice here as I am slightly confused. I need to install SSRS 2016 on an existing 4 node, cluster with AAAG.  I don't particularly need HA here or DR.  I guess I may as well read from my local secondary server as best practice. How would you implement this install? As SSRS is not […]
Powershell
Remove comma inside of double quotes - I have a file comma delimited that has no header. How can I clean the extra comma inside of my double quotes? It sees that comma and adds and extra field to my output. This is a short example data, my file has 37 columns and have found it in 2 fields for now, but […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -