Avatar of marrowyung
marrowyung
 asked on

SQL server performance baseline

hi,

usually what tools you guys build a performance baseline before rebuild index done ?

I want to bench mark that.
Microsoft SQL Server

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
Vitor Montalvão

No tools. Just take the average time for the query before the rebuild and then after so you can compare both.
marrowyung

ASKER
yes, this is the only thing I am doing but I want to do in a much robust way so that I can scan/check it again AFTER I rebuilt index.
marrowyung

ASKER
Matt is looking at my questino....
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Vitor Montalvão

I don't know what else you can add for your benchmark.
Fragmentation level and Query execution plan and times should be the information related to index that you may want to keep.
marrowyung

ASKER
"Fragmentation level" yes but management don't look at that. I will use ola solution for that, but some times I found ola has miss index to rebuild.

" Query execution plan "

we might just see EE is smaller and not that complex, but again, management don't read that.
Vitor Montalvão

If it's  a report for Managers then just write about the database reduction size and query performance after rebuild.
And Ola's script has everything you need to rebuild indexes. Is more than tested over all these years by hundreds or even thousands of DBAs.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
" query performance after rebuild."

noted that already but should I ask dev team to bench mark their application?

 "And Ola's script has everything you need to rebuild indexes. Is more than tested over all these years by hundreds or even thousands of DBAs."

I knew and that's why I use it.
Vitor Montalvão

Developers can (or should) only obtain data from Development Environment that usually isn't the same for Production Environment.
IMO, Application Managers should be the ones to provide those benchmarks. Developers are there to fix and improve based in the benchmark reports.
marrowyung

ASKER
"Developers can (or should) only obtain data from Development Environment that usually isn't the same for Production Environment.


I understand, always not the same, so i ask them anyway.

"Application Managers should be the ones to provide those benchmarks"

Excellent.

ok one last thing, They can say they don't know/have and I ask them to prepare one and they don't know how. :):),  I am not sure if DB monitoring tools will give this as I never make use of that before but per queries bench marking.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
that's why I don't know why recently developer blame DBA on why their web system is slow !

the coder can hide!

some US culture can do sth like they build the code, they don't care it is fast or slow, then push to DB team for tuning.

This is the way of querying the DB make it slow. if they don't learn how to code right in SQL server, I always can't see why they are hired.

in your side, who are expected to tune the SQL code ? DBA or developer ? one DBA can't tune all code by all developer.
marrowyung

ASKER
tks
Vitor Montalvão

in your side, who are expected to tune the SQL code ? DBA or developer ? one DBA can't tune all code by all developer.
In a first instance, developers but DBA can help them tune if they find something that is taking too long to complete. But this is the exception and not the rule. DBAs should work more on the server level (Resources, Security, Monitoring, Backups, ...) and Developers on the database level (permissions, queries, ...).
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
"Developers on the database level (permissions, queries, ...)."

for permission level , I will move it to DBA, specially infrastructure DBA like me, this can keep me busy.... :):)

queries is more on developer from my point of view as it involve business logic.

I tried to tune queries for developer and verified number of rows is the same, finally developer STILL says that my logic is wrong !!
Vitor Montalvão

for permission level , I will move it to DBA, specially infrastructure DBA like me, this can keep me busy.
I'm not talking about permissions on SQL Server instance (Logins) but on database level (users) as who can perform SELECT, INSERT, DELETE or UPDATE, who can execute stored procedures or functions. Creation of database role and schemas. This is more at logical level and a DBA shouldn't interfere.
marrowyung

ASKER
"I'm not talking about permissions on SQL Server instance (Logins) but on database level (users) as who can perform SELECT, INSERT, DELETE or UPDATE, who can execute stored procedures or functions. "

I'd like to classify it still DBA's job to make them has more job to do.

"This is more at logical level "

business logic ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Vitor Montalvão

business logic ?
Correct. DBAs doesn't have nothing to do with application requirements in terms of who should have access to what (tables, views, stored procedures, functions, ...). That's a development thing.
marrowyung

ASKER
ok. here, defination is a bit diff.
marrowyung

ASKER
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.