1

If you have a SQL Server 2012 database with test data in development and preproduction phase and the real from the company's server take place only in the production phase. In other words, the real data take place only the the production phase and not in the test and pre production phase.

Production phase take place inside in the company's server.

When you have a stored procedure that takes a lot of time to retrieve the data in the production phase and you need to make a performance tuning.

This specific stored procedure is part of critical function for a webpage. The stored procedure is used daily.

My questions are:

  1. Is it recommended to do a performance tuning in the production phase in relation to real data?

  2. Is it something that I should be aware of? Today, I'm a newbie in performance tuning.

  3. What plugin or tool do you use in order to review the the database and its specific stored procedure's performance?

Thanks!

10
  • So is the gist of your post "How to tune a stored procedure that is fast on test data but slow in production"?
    – Andriy M
    Aug 3 '15 at 7:13
  • 1
    Does the development database have the same data volume and data distribution as the production system? Or is the development database running with a relatively small amount of data compared to production? If your development database doesn't have a similar data volume and distribution, it's going to be almost impossible to do any sort of meaningful performance analysis in that environment. Aug 3 '15 at 8:23
  • 1
  • 1
  • 3
    If you can't restore a production backup in your test environment, you can simulate production data distribution using what's called a stats-only (clone) database. This won't allow you to see actual runtime performance but it should allow you to see what plans SQL Server would come up with based on minor changes to production queries or indexes (just make sure to turn off all the auto-create/update stats options). Aug 3 '15 at 15:16
1

"Performance" is a much broader topic than is being assumed here. There are quite a few factors that influence performance:

  • The Data Model: Are you using appropriate data types? Do you have Primary and Foreign Keys defined? Do you have repeating string values (bad) instead of lookup tables? Do you have an appropriate structure or something that is convoluted and/or over-engineered

  • Number of rows in the table(s): All things being equal, the Query Optimizer can choose to do things differently depending on the overall row-count of the table(s) involved in each query. Hence, testing in environments with radically different data volumes (not just distribution differences) can only test functionality, not performance.

  • Distribution of data values: This obviously affects statistics associated with indexes, but assuming that the "auto-create statistics" option is enabled (and it is by default), then any column used for filtering and/or sorting can have system-created statistics associated with it.

  • Indexes: The fact that indexes help find data should be obvious, but incorrectly defined indexes can sometimes hurt. Also, too many indexes can easily hurt DML operations (especially in relation to available hardware and contention).

  • Up-to-date statistics: Even if you have the same indexes between environments, and the exact same data, if the statistics are out of date then you can still see performance issues if the Query Optimizer doesn't know to use a certain index, or accesses it incorrectly due to obsolete row estimates.

  • Contention: What other queries are running on the system? Production will naturally have a lot more concurrent usage than a Development / QA / UAT system. More concurrent queries means more locking and less available hardware resources.

  • Code: There are usually several ways to achieve a goal, but they can have radically different requirements. There are many ways to write queries across the spectrum of horrible to great. And there are ways of structuring operations that are far better or worse than other methods. Often enough, even small changes in query and/or process structure can have major impact, regardless of any of these other factors.

  • Hardware: RAM (how much exists, how fast is it, and how much is available), CPU (how many cores, how fast are they, how many are allocated to SQL Server, how much is available), and Disk I/O (type of disk: SATA vs SAS vs ?, NAS vs SAN vs Local vs ?, Raid level, SSD vs non-SSD, etc, available throughput). For all of these areas, other processes running on the system can take up RAM, CPU, and I/O, which affects how much of the total amount is available to SQL Server to use. Meaning, tons of CPU does not help if most of it is being used by one or more programs outside of SQL Server.

  • Configuration: Things like Snapshot Isolation, Async Update Statistics, Auto-growth, Auto-shrink, etc can impact performance. Are they configured the same between Dev / QA / Production environments? Are they configured correctly to begin with?

Generally speaking, even horribly inefficient code and/or structure will appear to work quickly enough in most Development and QA environments. With only 100 - 1000 rows of data, poor choices in data modeling and/or coding will be hidden.

At the very least you should have, in a non-Production environment, the exact same structure and configuration (tables, indexes, database, server, etc) that Production has. At this stage you should be focused on a good, scalable data model as well as good T-SQL coding.

Indexes are a bit trickier as they require a representative data set (volume and distribution) since that will impact the execution plan. This can be done in one or more testing environments (QA, UAT, ?). If it is not possible to get this data into a lower environment due to sheer size, then this will have to be done in Production.

But, even with this, true performance tuning can really only be done in Production since that is "reality" whereas the other environments are not (unless you somehow manage to duplicate not only the hardware, but also the usage / load). So you do your best, educated guess in lower environments but you will only know in the end once it is in Production. For testing code changes, I have found it helpful to create a global temporary stored procedure so that I can do side-by-side comparisons in the ideal environment.

0

When you have a stored procedure that takes a lot of time to retrieve the data in the production phase and you need to make a performance tuning.

This specific stored procedure is part of critical function for a webpage. The stored procedure is used daily.

My questions are:

Is it recommended to do a performance tuning in the production phase in relation to real data?

Performance tuning should be an integral part of development. Let me explain.

When development is developing code for your application, they should be given a near to production type of data (backup PROD data --> restore on a load testing environment --> mask all sensitive/PII data) that they can test their code once they are done developing. An experienced DBA (preferably with development background) should assist them if needed to check the statistics io as well as query plans are fine and there are no anti-patterns in the queries.

In my company we have a separate environment (Performance Testing Environment) wherein we simulate a real user workload on near to real PROD data and we trace what pages are loading slow (e.g. you can use opensource tools like Mini Profiler) and OpServer (Paid one - SQLSentry is the best).

There are tools like redgate's data generator (not free - Paid!) which does an amazing job when you want to generate real test data.

What plugin or tool do you use in order to review the the database and its specific stored procedure's performance?

Check out my answer to SQL server tools to identify query optimizations

For completeness, you can even fake hardware (if you dont have budget of getting a good testing environment) - Faking Hardware NUMA on a SMP Machine for Learning and Demos - BCDEdit

Remus has an excellent blog post on - How to analyse SQL Server performance ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.