RSS

Category Archives: SQL Server 2014

SQL Server Torn Page Repair: Without Worrying About Data Loss or Corruption

It can often be bugs and errors in fetching data from the server. This can leave system administrators baffled as all work comes to a standstill. Incomplete or incorrect transaction cause confusion among employees working on the different sections of a database.

One such frequent issue is the SQL Server torn page error. It can be quite a hassle for a non-technical user facing such a situation. There are ways to fix it easily without worrying about any data loss. This post covers all there is to know about this topic. Continue reading to find out more.

What is the Torn Page in SQL Server?

It is the inability of the server to fetch a particular data during a transaction. It is caused when an Input/Output header tries to access a page that was written incorrectly to the disk. It reports a message saying ‘I/O error (torn page) detected during read’. The reason for this can be primarily contributed to power failure causing partial writes. Other factors include damaged disk or other hardware to which data is being written to. If a torn page is detected by SQL Server, it will sever all connections as the requested data is inaccessible. During the restore process, detecting a torn page sends the database into SUSPECT mode.

sql server torn page repair

Read the rest of this entry »

 

Query Store for Solving Query Performance Regressions

Query Store for Solving Query Performance Regressions

Query performance is a very important area of SQL server. We always have badly performance queries around.

Query store is the newest tool for tracking and resolving performance problems in SQL server.

In this article, we are going to have a look at some practical uses of SQL server Query store.

What is Query Store?

The query store has been described by Microsoft as a ‘flight data recorder’ for SQL server queries. It tracks the queries run against the database, recording the details of the queries and their plans and their runtime characteristics. Query store is per database feature and runs automatically. Once turned on, nothing further needs to be done to get it to track data. It runs in the background collecting data and storing it for later analysis.

Query store is available in SQL Server 2016 and later, and Azure SQLDB v12 and later. It is available in all editions of SQL server, even in Express edition.

How is Query store different from other tracking options?

We have had query performance tracking for some time though in the form of dynamic management views. Mostly, sys.dm_exec_query_stats and sys.dm_exec_query_plan and tracing tools like SQL server profiler and extended events.

So, what makes Query Store different? Let me start answering that by describing a scenario that I encountered a couple of years ago.

A particular critical system was suddenly performing badly. It had been fine the previous week and there have been no extended events sessions or profiler traces running historically. The admin had restarted the server when the performance problem started, just to make sure it was not something related to a pending reboot.

As such, there was no historical performance data at all and solving the problem of what happened, why the query performance is different this week was extremely difficult.

1

Read the rest of this entry »

 

Tags: ,

Improve SQL Server Performance with Compression

Improve SQL Server Performance with Compression

SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.

a1

The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Read the rest of this entry »

 

Tags: , , , ,

SQL Server performance – measure Disk Response Time

Introduction

As DBAs, we all get to the point where we are asked to setup a new server for a particular environment. Setting up a new server is not a big thing but giving the answer to the question that “how well it will work” might be tricky.

There are tons of items which we can set up to measure how well the newly installed server will receive a response, but here I will discuss one of the most valuable resources of the server “Disk.” Most often the disk is not measured correctly, or I have seen environments where the disk response time has never been measured. I will discuss here a tool from Microsoft which is very handy and can solve your problem very quickly. The diskspd.exe!

It’s the superseding version of SQLIO which was previously used to measure IO response time for the disk. The source code of diskspd.exe is hosted on GitHub. You can download this free utility from Microsoft’s website using this link.

After you download the utility, you will get a zip file. Just unzip the file, and it will give you the folders and files as shown in the below screenshot. You will-will need the eye of diskspd inside the folder “amd64fre” if you have a SQL Server 64-bit version (most of us will be having this).

To complete this article please check it here

 

word-image-29

 
Leave a comment

Posted by on February 16, 2017 in General topics, SQL Server 2014

 

Tags: , , , , ,

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

One of customers changed the value returned from @@SERVERNAME.  SQL Server works no problem, however an unexpected behavior appeared.  Changing the value for @@SERVERNAME, caused the backups to fail.

Looking at the maintenance jobs, found all jobs completed successfully and without issues.  However, upon looking at the database’s statics it states no backups completed.

image

Because the database in question is part of AlwaysOn Availability Group (AG); SQL Server executes sys.fn_hadr_backup_is_preferred_replica to determine if the backup should take place on the current node.  However, it returns value of 0 for all databases, if the preferred replica is set.  Because, the script makes a check that is running on the server that is preferred.  It does this by comparing the value to @@SERVERNAME to value of replica_server_name in sys.availability_replicas.  Because value will never match, it skips the database on both primary and secondary replica.

I have created a Microsoft Connect article (link); asking this little bit of information to be added to Books Online article (link).  There was a request submitted by Ola Hallengren (Blog | Twitter), which was closed as Won’t Fix (link).   Please vote!

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
 

Tags:

SQL Server 2005’s end-of-life clock is ticking down

After 10 great years, extended support for all versions of SQL Server 2005 is coming to an end on April 12, 2016 it is the time for Upgrade to SQL Server 2014

Upgrading is not just a maintenance task, but an opportunity to provide new value to your business. Moving to SQL Server 2014 or Azure SQL Database enables you to achieve breakthrough performance:

  • SQL Server 2014 has been benchmarked to be 13 times faster than SQL Server 2005, before the additional performance gains available from in-memory OLTP.
  • AlwaysOn availability groups provide more reliable High Availability.
  • Together with Windows Server 2012 R2 you can scale up across compute, networking and storage.
  • New tools in SQL Server Management Studio provide an easy on-ramp to cloud to help you get more from your data platform investment.

fa1f03a8-7ff3-41c6-8849-7500136bfee9-original

Reference

http://blogs.microsoft.com/blog/2015/04/13/are-you-still-running-sql-server-2005-now-is-the-time-to-upgrade-to-a-modern-data-platform/

Six Benefits to Planning for SQL Server 2005 and Windows Server 2003 End of Support Now

http://blogs.technet.com/b/dataplatforminsider/archive/2014/12/16/six-benefits-to-planning-for-sql-server-2005-and-windows-server-2003-end-of-support-now.aspx

http://www.infoworld.com/article/2910315/database/sql-server-2005s-end-of-life-clock-is-ticking-down.html

 

 

 
Leave a comment

Posted by on December 20, 2015 in General topics, SQL Server 2014

 

Tags: ,

SQL Server 2012 NUMA Node Imbalance–Conclusion

Final post in the series, I hope to summarize my findings for everyone.  Thanks for all the comments, feedback and comments I have received to-date on these posts. 

Before providing the summary of all the testing, research and reading, I want to provide some internals for folks.

NUAM Memory Allocation

If a server has NUMA configuration, when we define the max memory setting.  All memory gets allocated evenly from all NUMA nodes.  For example, if I allocate 8GB from a server that has 4 NUMA nodes, it will allocate 2GB from each NUMA node for the instance in question. So be careful, if you are using CPU affinity.  As even using CPU affinity, lets say you only want to use NUMA 1 for an instance; this can lead to foreign memory access. NUMA allocation still happens across all NUMA nodes (at least in SQL Server 2014).  So even though you might only have subset of schedulers online, memory will still be accessed across all NUMA Nodes.  Even though with new hardware foreign memory access does not have major impact on performance.  Still recommend avoiding it.  If you must use CPU affinity, then allocate sub-set of the processor from each NUMA node.

Lazy Writer Responsibility

Lazy Writer is back ground process and a thread is created per physical NUMA node (for Soft-NUMA, we only get one thread, you can read details here by Jonathan Kehayias (Blog|Twitter)). Lazy Writer responsibility is to make sure sufficient number of free pages, about 640 pages, are always available for new data to be loaded.  If Lazy Writer thread is spinning, that is usually a clear signal of internal memory pressure. 

Note: Internal Memory pressure does not mean server doesn’t have enough memory.  There are multiple factors that can contribute to this, one fact is low Max Server Memory, which is what has been explored in these articles.

Original Scenario: SQL Server 2012 Latest Build + Windows 2008 R2 Latest Build + Locked Pages In Memory + Low Max Server Memory + AMD Processors + NUMA Configuration

In this build, I can consistently reproduce the issue.  If I set the Max Memory low enough, it caused the Lazy Writer on NUMA node 0 to spin non-stop, as I documented in SQL Server 2012 NUMA Node Imbalance – Cont’d.  To further to this I understand some additional internals around this thanks to colleague who helped here.  As I was not true sure what is happening. 

If we looked at the DBCC MEMORYSTATUS, in particular we notice following for Node 0:

Memory node Id = 0                       KB
—————————————- ———–
VM Reserved                              127086340
VM Committed                             642452
Locked Pages Allocated                   55996
Pages Allocated                          54008
Pages Free                               0
Target Committed                         512000
Current Committed                        698448
Foreign Committed                        0
Away Committed                           0
Taken Away Committed                     0

Review the two numbers high-lighted above, Target memory is what this NUMA node is allocated and Current is what is NUMA consuming, therefore the Page Free count is 0.  Thus Lazy Writer spinning to free up memory.  However it is unable, thus spinning indefinitely causing CPU to pin.  Question to answer, why can’t Lazy Writer free up memory from NUMA Node 0?

Looking further into MEMORYSTATUS, we also notice, that most of the memory allocated into NUMA Node 0 is for buffer cache:

MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
—————————————- ———–
VM Reserved                              103971820
VM Committed                             524288
Locked Pages Allocated                   2524
SM Reserved                              0
SM Committed                             0
Pages Allocated                          8

Test Scenario #1: Windows 10 + SQL Server 2012 RTM + Low Max Server Memory + Intel Processors + No-NUMA (Physical)

In this, I was just trying to reproduce the issue on my desktop, with no luck.  Memory allocation or such were nominal.

Test Scenario #2: Windows 2012 + SQL Server 2014 RTM + Low Max Server Memory + Intel Processors + No-NUMA (Hyper-V)

Again no issue, as per the title of the series, NUMA Imbalance.  It was obvious and expected this two scenarios don’t show any issues.  But I wanted to confirm it.

Test Scenario #3: SQL Server 2008 R2 + Windows 2008 R2 + Low Max Server Memory + AMD Processors

These test was on same servers as original scenario, however we did not notice any issue.

*** During testing, we realized an interesting fact about NUMA configuration in SQL Server 2008 R2.  SQL Server swaps NUMA 0 and NUMA 1 CPU mapping when creating a logical mapping for schedulers.  For example, if you have 2 NUMA Nodes each with 4 cores; SQL Server binds NUMA 0 – Processor 0 – 3 to Scheduler 4 – 7 and NUMA 1 – Processor 4 – 7 to Scheduler 0 – 3.  This is considered NUMA Swap, documented in this MSDN blog article here by CSS Team.  However this behavior does not seem to persist in SQL Server 2012.

Test Scenario #4: SQL Server 2014 + Windows 2012 R2 + Low Max Server Memory + Intel Processors + NUMA (Azure IaaS Virtual Machine)

This was interesting case, when I set the Max Server Memory to low value, the NUMA node 0’s processor time started to increase.  However it was not as swear as SQL Server 2012, however these are different processors.  I had same behavior as noticed above, Current Committed was higher then Target Committed, therefore NUMA thread on Node 0 was spinning.  However interesting thing note, it was not as aggressive as SQL Server 2012.  It was not continuously spinning, it allowed CPU to breath.  However would wake up frequently as low as ever 20ms.

!!! Eliminated Locked Pages in Memory as a factor for NUMA Imbalance.

Test Scenario #5: SQL Server 2014 + Windows 2012 R2 + Low Max Server Memory + Intel Processors + Locked Pages In Memory + NUMA (Azure IaaS Virtual Machine)

Exact same behavior as above was noticed, however, after restart, SQL Server no longer had Current Committed higher then Target Committed.  Even after lowering the max memory even further then Scenario #4.  It did not cause as dramatic shift.

*** Another interesting thing to note in SQL Server 2014. Up-to SQL Server 2012, Lazy Writer Thread bound to CPU 0 of each NUMA Node.  In SQL Server 2014, Lazy Writer Thread is binding to last CPU of each NUMA Node.

Conclusion & Things to Consider

  • Configuring SQL Server Max Server Memory low memory will cause Lazy Writer to spin due to internal memory pressure issue.
  • There is no value at which point this behavior starts, it depends on the load of the server.  For example, in Original Scenario, due to server load under 4000MB it started spinning the Lazy Writer thread; however in Test Scenario #4, I had to go down to as much as 1000MB. Therefore on your servers look at Lazy Writer thread in conjunction with CPU and DBCC MEMORYSTATUS to understand what is truly happening.
  • This behavior is consistent in whether I have Locked Pages in Memory or not.
  • In Windows 2012 R2 NUMA allocation seem to be much better then Windows 2008 R2.
  • SQL Server 2014 Lazy Writer thread does not seem to be as aggressive as SQL Server 2012.
  • Few KB articles came up during my research in SQL Server 2012 that fix NUMA related issues, KB2819662 and KB2926223.
  • There as been some fixes released for NUMA for Windows 2008 R2 operating system also, please review KB2155311 and KB2510206.
  • In addition, if you are running a server with more then 64-logical cores, please review K-Group Configuration, for details please reference this article.
  • Last but not least verify that memory is distributed evenly on physical server.  That is the memory banks have been evenly allocated, i.e. you do not have more memory allocated in one bank versus another, as you might also see one NUMA node working extra harder if there is significant amount of foreign memory access.

Outstanding Question

Question to answer, why can’t Lazy Writer free up memory from NUMA Node 0?

SQL Server Lazy Writer is only responsible for clearing memory from Buffer Pool, it cannot remove or clear memory from any other memory clerk.  Therefore, it is possible, that other internal components have memory allocated on NUMA Node 0 only, therefore NUMA Node 0 is unable to free up enough memory for buffer pool only.  Looking at DBCC MEMORYSTATUS we can see which memory clerks allocate from NODE Node 0 only, there are few, for example, MEMORYCLERK_SQLCLR, MEMORYCLERK_SOSMEMMANAGER, OBJECTSTORE_LOCK_MANAGER, and MEMORYCLERK_XE_BUFFER all have allocation from NODE Node 0 only.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on October 5, 2015 in SQL Server 2012, SQL Server 2014

 

Tags: , , , , ,

 
%d bloggers like this: