Query Store Usage Scenarios

What is Query Store?

Query Store is a tool to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis.

query-store-process-3

So, if query store stores my query information, what kind of scenarios I could use this information?

  1. To fix queries with plan choice regressions is one example where to use it. Query Optimizer may decide to choose a different plan and without Query Store would be difficult to identify and fix it.
  2.  Tune top resource consuming queries is much more easier with Query Store because this tool will show the plan is using in a query that has high reads or duration for example.
  3. To compare workload performance before and after the application change or adding new hardware, create indexes or the next scenario SQL Server updates.
  4. To keep performance stability during the upgrade to newer SQL Serverquery-store-usage-5
  5. To improve ad hoc workloads. Run the Transact-SQL script bellow to get total number of query texts, queries, and plans in the system and determine how different they are by comparing their query_hash and plan_hash

/*Do cardinality analysis when suspect on ad hoc workloads*/
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query;
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;
SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan; 

 

SQL Server Undocumented 2

One more undocumented function/commands. The fn_dump_dblog function is used to read transaction logs from a log backup file.

This is an example fo the undocumented function to read transaction logs:

SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser 
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Backup_Logfile.trn',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

A good example to use it is when I tried to restore up to specific LSN using the command

RESTORE LOG  WITH STOPBEFOREMARK = ‘lsn:’;

To read your current log file the function fn_dblog is used. For more information about this function click here

Follow bellow the operations is returned for the function:

OPERATION DESCRIPTION
LOP_ABORT_XACT   Indicates that a transaction was aborted and rolled back.
LOP_BEGIN_CKPT  A checkpoint has begun.
LOP_BEGIN_XACT  Indicates the start of a transaction.
LOP_BUF_WRITE Writing to Buffer.
LOP_COMMIT_XACT Indicates that a transaction has committed.
LOP_COUNT_DELTA  ?
LOP_CREATE_ALLOCCHAIN New Allocation chain
LOP_CREATE_INDEX Creating an index.
LOP_DELETE_ROWS Rows were deleted from a table.
LOP_DELETE_SPLIT  A page split has occurred. Rows have moved physically.
LOP_DELTA_SYSIND   SYSINDEXES table has been modified.
LOP_DROP_INDEX Dropping an index.
LOP_END_CKPT Checkpoint has finished.
LOP_EXPUNGE_ROWS Row physically expunged from a page, now free for new rows.
LOP_FILE_HDR_MODIF   SQL Server has grown a database file.
LOP_FORGET_XACT Shows that a 2-phase commit transaction was rolled back.
LOP_FORMAT_PAGE   Write a header of a newly allocated database page.
LOP_HOBT_DDL  ?
LOP_HOBT_DELTA  ?
LOP_IDENT_NEWVAL Identity’s New reseed values
LOP_INSERT_ROWS   Insert a row into a user or system table.
LOP_LOCK_XACT
LOP_MARK_DDL Data Definition Language change – table schema was modified.
LOP_MARK_SAVEPOINT Designate that an application has issued a ‘SAVE TRANSACTION’ command.
LOP_MIGRATE_LOCKS
LOP_MODIFY_COLUMNS    Designates that a row was modified as the result of an Update command.
LOP_MODIFY_HEADER   A new data page created and has initialized the header of that page.
LOP_MODIFY_ROW   Row modification as a result of an Update command.
LOP_PREP_XACT Transaction is in a 2-phase commit protocol.
LOP_SET_BITS
LOP_SET_BITS Designates that the DBMS modified space allocation bits as the result of allocating a new extent.
LOP_SET_FREE_SPACE   Designates that a previously allocated extent has been returned to the free pool.
LOP_SORT_BEGIN  A sort begins with index creation. – SORT_END end of the sorting while creating an index.
LOP_SORT_EXTENT Sorting extents as part of building an index.
LOP_UNDO_DELETE_SPLIT The page split process has been dumped.
LOP_XACT_CKPT During the Checkpoint, open transactions were detected.

Logging

Every database has a transaction log that records all transactions and the database modifications made by each transaction

The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state.

How that works? What steps SQL Server do to keep the consistent state?

Taking a example I want to update 10 rows in my table and those rows are using 2 pages, let’s see step by step SQL Server does.

  1. Read the pages from buffer pool, if the pages isn’t there SQL Server will get that data from disk and put in memory.
  2. Start the lock process, SQL Server will acquire intent-exclusive lock for the table and pages, update row-level locks.
  3. After all rows to be updates are locked, it’s time to convert the update lock to exclusive lock and make the change to the page in memory. This process will create a log record describing the changes were made.
  4. Next the transaction is ready to commit. This step will write the log records to the transaction log on disk and will acknowledge the commit to the user.

The changed data still in the buffer pool and written in the transaction log on disk.  The data will stay in memory util a checkpoint runs and write the changes to the data files.

9012805ba7742e521e941ec1ec5838b1--sql-server-logs

So, that’s why transaction logs are critical and we have to take care of it.

How to manage the transaction log

How to read transaction logs

 

Wait wait wait…

Let’s talk about why we have to wait and how to understand the wait types.

Paul Randal in his post Wait statistics, or please tell me where it hurts said:

A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called being RUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it’s resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed

That’s explain a lot, because the SQL Server threads doesn’t run all in the same time. A good example is when our query is doing physical reads. The IO subsystem is the slowest part of our resources and probably will take some time if the query is reading gigabytes of data.

Capture

After the CPU request the data from the disk, the disk will run for it, but before send the data back. All data need to go to memory first and that may don’t have the necessary space.  The thread is going to wait until some resources been released first. There are many scenarios, for example, how many threads are running this query? How long will take to the application to show that data?

So, every time a thread needs to wait for a resource it will increase a wait time type, such as PAGEIOLATCH_XX , PAGELATCH_XX, ASYNC_NETWORK_IO, CXPACKET, RESOURCE_SEMAPHORE. I will talk more about waits in the next posts.

Heap Tables

What’s a heap table? I would say it’s a table without clustered index.

What’s the characteristic of a heap table? The data isn’t ordered.

What’s the consequence having a heap table? There are a few:

  1. Specific data is not retrived quickly
  2. Data pages aren’t linked, that means sequential access needs to refer to the index allocation map (IAM) pages
  3. No cost to update indexes
  4. No additional space to store clustered index

SQL Operations Studio Linux

Great news, a visual way to manage SQL Server from Linux.

SQL Operations Studio came to fill a gap we were waiting to develop and manage SQL Server databases on Linux. In the link https://github.com/Microsoft/sqlopsstudio we can see more information like the features below.

Feature Highlights

  • Cross-Platform DB management for Windows, macOS and Linux with simple XCopy deployment
  • SQL Server Connection Management with Connection Dialog, Server Groups, and Registered Servers
  • Object Explorer supporting schema browsing and contextual command execution
  • T-SQL Query Editor with advanced coding features such as autosuggestions, error diagnostics, tooltips, formatting and peek definition
  • Query Results Viewer with advanced data grid supporting large result sets, export to JSON\CSV\Excel, query plan and charting
  • Management Dashboard supporting customizable widgets with drill-through actionable insights
  • Visual Data Editor that enables direct row insertion, update and deletion into tables
  • Backup and Restore dialogs that enables advanced customization and remote filesystem browsing, configured tasks can be executed or scripted
  • Task History window to view current task execution status, completion results with error messages and task T-SQL scripting
  • Scripting support to generate CREATE, SELECT and DROP statements for database objects
  • Workspaces with full Git integration and Find In Files support to managing T-SQL script libraries
  • Modern light-weight shell with theming, user settings, full screen support, integrated terminal and numerous other features

Let’s see how it works:

The process is very simple. First step download de file in this link https://go.microsoft.com/fwlink/?linkid=862646

After download the next step is to extract the files in a folder wherever you want and run the sqlops file.

My first impression is quite good. A good visual tool to work with SQL Server on Linux. I like the execution plan view and how to manage the databases.

sqlops1

 

This slideshow requires JavaScript.

Comparing with SQL Server Management Studio I miss some options such as:

  • LinkedServer Objects
  • Maintenance Plans
  • Replication
  • Database Mail
  • Extended Events

The conclusion is I really can work on that and run all my scripts and create objects like linkedservers, extended events or replications. That helps a lot to try a new platform concepts.

 

Script to get the tables size in SQL Server

A simple script to know how much space the tables are taking from the disk.

SELECT t.name AS TableName
, s.name AS SchemaName
, p.rows AS RowCounts
, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalMB
, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedMB
, CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.name
, s.name
, p.rows
ORDER BY 5 DESC; Continue reading “Script to get the tables size in SQL Server”

How Update works?

Update data in SQL Server is a simple task. Using the command update table set column = value where column = value. Don’t forget the where clause :).

But, what SQL Server does internally? Delete plus Insert? Modify? Well, the answer depends.

Let’s see examples how that works.

First, create a database and table for the test.

createdatebase

As you can see, I didn’t create an index so I will update a heap table. Before update the row, I will drop the log register and then update the row I want.

firstupdate

Ok, now the result of the DBCC LOG

updateCol2

As we can see, the result is a modified row in the log, what about using an index? Creating a nonclustered index in the column I’m updating.

nonclusteredindexupdate

nonclusteredindexupdateresult

The result is very different, now we can see a modified row in the heap and in the nonclustered index I got a delete/insert row. Very nice!

What if was a clustered index?

clusteredindex

I dropped the nonclustered index and created a clustered and result is:

clusteredindexresult

Again, only the modified row in the index clustered. And, if we try to update our key in the clustered index?

clusteredindexkey

clusteredindexkeyresult

I got the result with delete/insert as I was expecting because I changed the key like the nonclustered index. Now, if I try to update the row with the same value, what SQL Server will do?

noupdate

Nothing, no change at all.

SQL Examiner Tool

I like tools and I’ve used a couple of them. I had the opportunity to try SQL Examiner to compare database structures and even though compare data. The tool is quite simple to configure the options.

Talking about comparison, well we compare one database with another. Imagine a situation, there are two environments (development and test) and we must deploy the changes from one to another.

Let’s see step by step how it works

The steps are quite simple, need to select source and target type (Microsoft SQL Server, Oracle, MySQL, PostgreSQL, SQL Azure). After that, define the servers name, the authentication mode and the database. Below I’m showing how to connect in 2 local instances I have on my local computer.

img2

After that, the SQL Examiner will compare what differences exist between databases.Now, I’d say I was expecting my objects differences and I got a great feature. In the image below the SQL Examiner is comparing the database options as well.

This is fantastic, the tool is showing different compatibility levels and it can be very helpful if you are doing performance tests, for example, the behavior is very different in this case. Also, I can compare my production environment and keep the database configurations equal in all environments.

img4

Great tool, simple and efficient.

10 Configurations Best Practices

downloadThe first checklist before start using any application using SQL Server should be the default configurations.

What configurations I used to change?

  1. Max Server Memory: SQL Server will grab all memory available and that will cause concurrency with the OS
  2. MAXDOP: for OLTP also yes, the good thing is to use it
  3. Cost Threshold for Parallelism: the default value is too low, and the number will depend on the environment, I used to monitor and change as needed. I use to start with 40
  4. Model File Sizes: the model database will provide information when need to create more databases. (initial size and autogrowth are way too small and need to avoid file system fragmentation)
  5. Tempdb Files: look how many CPUs in the server and if the number is less than 8, so would be the number of CPUs, if it is more, so, start with 8 and the files should be the same size and the same autogrowth.
  6. Database Mail: used to send alerts, very important to get notified when something bad happens.
  7. Backup Compression: the benefit will be much more than the CPU cost.
  8. Remote Dedicated Administrator Connection: provides dedicated CPU, memory, and scheduler and by default only works via RPD or physically on server
  9. Maintenance: start as soon as possible with checkdb, index maintenance, statistics, backups. In this case, I like Ola Hallengren SQL Server solution.
  10. The last but not least, Security: this is the most important in the list, user accounts or even DBAs as sysadmin, look my tip in MSSQLTips. In addition, disable sa, disable unnecessary logins.