SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What do you consider important when performance testing?


What do you consider important when performance testing?

Author
Message
Gary Varga
Gary Varga
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19609 Visits: 6534
Comments posted to this topic are about the item What do you consider important when performance testing?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Eric M Russell
Eric M Russell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16340 Visits: 10911
Recently I thought of something that had never previously crossed my mind: what about purposely testing against a database with fragmented indexes as may exist in a production database? Is this is a valid scenario? How do you deliberately create such data?

When a database is restored to another instance having the same version of SQL Server; the indexes, fragmentation, and statistics will be the same as when the backup was performed. So, if your QA process involves working from a recent restore of production, you should have a good base for comparison in that regard.

However, when performance testing in a QA environment, I don't think it's necessary to duplicate the physical environment of production to get a good idea of how it will perform in production. Even if the QA environment only has 10% the data volume as production; so long as the object schemas, configuration, and data cardinality are equivalent, the execution plans should be equivalent too. So, for example, if adding a new join and columns to a stored procedure results in 30% more page reads in QA, then you should expect the same proportional 30% increase in production. That's what you should focus on, how the latest changes modify the the plan and proportionally impact i/o, cpu, and memory, not runtime duration.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
quackhandle1975
quackhandle1975
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3490 Visits: 1242
One quick answer would be to create a load of VMs and do the testing from there, however if your prod instance(s) are physical there's a chance the results could be skewed due to difference in hardware.

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Eric M Russell
Eric M Russell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16340 Visits: 10911
Staging a high transaction volume OLTP environment for QA and performance testing is actually much harder than a data warehouse environment. Data Warehouses tend to be static with fewer users. However, even if you duplicate the OLTP physical environment, software, and database, it more difficult to predict how it will perform or behave when confronted with multi-users and concurrancy scenarios.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Gary Varga
Gary Varga
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19609 Visits: 6534
Eric M Russell (8/7/2014)
Staging a high transaction volume OLTP environment for QA and performance testing is actually much harder than a data warehouse environment. Data Warehouses tend to be static with fewer users. However, even if you duplicate the OLTP physical environment, software, and database, it more difficult to predict how it will perform or behave when confronted with multi-users and concurrancy scenarios.


There are plenty of tools, LoadRunner springs to mind, that can configured and scripted to exercise clients to replicate concurrency and multi-user scenarios.

It is the database instance that I have not thought through deeply enough. Hardware etc is easy, if not costly, to replicate as is the data.

I don't think that many people duplicate data's physical scenarios so I thought I would ask.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3394 Visits: 1694
Thanks Gaz! Testing is imperative and any test no matter how valid or invalid it is thought to be is welcomed. Sometimes the most poorly thought out test can emulate what happens in production under abnormal circumstances. Should we stress test the code and the database? Yes, why wouldn't we?

M.

Not all gray hairs are Dinosaurs!
The Fault
The Fault
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 316
Eric M Russell (8/7/2014)
Even if the QA environment only has 10% the data volume as production; so long as the object schemas, configuration, and data cardinality are equivalent, the execution plans should be equivalent too. So, for example, if adding a new join and columns to a stored procedure results in 30% more page reads in QA, then you should expect the same proportional 30% increase in production. That's what you should focus on, how the latest changes modify the the plan and proportionally impact i/o, cpu, and memory, not runtime duration.


Try offering to management the answer that runtime duration isn't important when end users are seeing slow responses after a recent change. It won't go down well!
Eric M Russell
Eric M Russell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16340 Visits: 10911
The Fault (8/7/2014)
Eric M Russell (8/7/2014)
Even if the QA environment only has 10% the data volume as production; so long as the object schemas, configuration, and data cardinality are equivalent, the execution plans should be equivalent too. So, for example, if adding a new join and columns to a stored procedure results in 30% more page reads in QA, then you should expect the same proportional 30% increase in production. That's what you should focus on, how the latest changes modify the the plan and proportionally impact i/o, cpu, and memory, not runtime duration.


Try offering to management the answer that runtime duration isn't important when end users are seeing slow responses after a recent change. It won't go down well!

Runtime duration is important. I'm just saying that i/o pages and execution plans are more useful for relative comparisons between two different environments.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3394 Visits: 1694
Eric M Russell (8/7/2014)
The Fault (8/7/2014)
Eric M Russell (8/7/2014)
Even if the QA environment only has 10% the data volume as production; so long as the object schemas, configuration, and data cardinality are equivalent, the execution plans should be equivalent too. So, for example, if adding a new join and columns to a stored procedure results in 30% more page reads in QA, then you should expect the same proportional 30% increase in production. That's what you should focus on, how the latest changes modify the the plan and proportionally impact i/o, cpu, and memory, not runtime duration.


Try offering to management the answer that runtime duration isn't important when end users are seeing slow responses after a recent change. It won't go down well!

Runtime duration is important. I'm just saying that i/o pages and execution plans are more useful for relative comparisons between two different environments.


And we do those relative comparisons between two different environments so that we can determine how to make the response more efficient, and faster. :-)

Not all gray hairs are Dinosaurs!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search