SQL server performance baseline

hi,

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

I want to bench mark that.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
No tools. Just take the average time for the query before the rebuild and then after so you can compare both.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Matt is looking at my questino....
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
" 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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
They can say they don't know/have and I ask them to prepare one and they don't know how
We usually expect that people performing they tasks know what they are doing. If not, then maybe they aren't the right person for that job.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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, ...).
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 !!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok. here, defination is a bit diff.
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.