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:
-
Is it recommended to do a performance tuning in the production phase in relation to real data?
-
Is it something that I should be aware of? Today, I'm a newbie in performance tuning.
-
What plugin or tool do you use in order to review the the database and its specific stored procedure's performance?
Thanks!
Best Answer
"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.