Improvements in SQL Server Environment

Question

Post reply

Improvements in SQL Server Environment

rkrpat

Right there with Babe

Points: 720
More actions
June 29, 2016 at 7:54 am

#319600

Hi All,
This is general question and not any Technical one.
Our SQL Server environment is stable and we are being chased for Service Improvements.
Could you please suggest any improvements that are standard DBA improvements.
I know there could be many, Please suggest.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

chased for Service Improvements

chased by whom?

what are they asking for?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Possibly obvious but shouldn't hurt to ask. Have you run sp_blitz? https://www.brentozar.com/blitz/

Or other health check scripts?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

rkrpat (6/29/2016)
Hi All,
This is general question and not any Technical one.
Our SQL Server environment is stable and we are being chased for Service Improvements.
Could you please suggest any improvements that are standard DBA improvements.
I know there could be many, Please suggest.

In the SSMS Object Explorer Window, right click on the instance name, select {Reports}, drill down to the performance reports for CPU and IO, do an analysis, and then start chasing back for all the junk code they need to fix. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 4

+1 on health checking your servers looking for performance improvements in both code and the maintenance being done on the SQL instances, are there indexes that need work more frequently as they tend to fragment quickly or their stats go out of date. Any missing or unused indexes? If they want service improvements this is the most important thing you can do and adds value.

Consider a performance monitoring tool if you do not already have one.

Look to automate as many processes as you can

---------------------------------------------------------------------

SQL Programmers SSC Rookie Points: 36 More actions · Answer 5

•CHECK THE SQL SERVER VERSION

Check for the latest SQL server version, which has the latest version of the SQL query optimizer. This will have enhanced development techniques that can help maximize the speed of your database.

•CHECK MEMORY USAGE

Previous versions of SQL Server max out at 64GB. Optimize the memory settings in order to to perform larger joins and sorting queries.

•CHECK THE TASK MANAGER

In the latest version of SQL Server, check the task manager to identify a virus installation, find the RDP of other users, and remove a slow server.

•CHECK LOG REPORTS

In the SQL Server and Windows log, problems can be identified and rectified or enhanced. In many cases, removing hardware problems will help poor performance.

oCHECK FOR MULTI-SERVER QUERIES

It is possible to query multiple databases at the same time with results appearing in the same window. This can slow down database performance.

oCHECK THE QUERY EDITOR

Various enhancements in the query browser (like a debugger) may increase the producti...

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Welcome aboard but...

SQL Programmers (7/6/2016)
•CHECK THE SQL SERVER VERSION
Check for the latest SQL server version, which has the latest version of the SQL query optimizer. This will have enhanced development techniques that can help maximize the speed of your database.

Like what? Got any examples or reference links?

•CHECK MEMORY USAGE
Previous versions of SQL Server max out at 64GB. Optimize the memory settings in order to to perform larger joins and sorting queries.

Not true. The Enterprise Edition could and can work with a lot more. Again... any reference links or suggestions as to what the optimal settings might be?

•CHECK THE TASK MANAGER
In the latest version of SQL Server, check the task manager to identify a virus installation, find the RDP of other users, and remove a slow server.

"Remove a slow server"????? :blink:

•CHECK LOG REPORTS
In the SQL Server and Windows log, problems can be identified and rectified or enhanced. In many cases, removing hardware problems will help poor performance.

Again, how so? This is a bit like saying "It's easy to get to the moon... you just need to build a rocket ship". 😉

oCHECK FOR MULTI-SERVER QUERIES
It is possible to query multiple databases at the same time with results appearing in the same window. This can slow down database performance.

So can having more than 1 user. Can you be a little more specific?

oCHECK THE QUERY EDITOR
Various enhancements in the query browser (like a debugger) may increase the producti...

It takes some pretty hefty privs to use the debugger. Privs that a lot of Developers don't have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Was that post a downgraded version of this one: https://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/ ?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Luis Cazares (7/7/2016)
Was that post a downgraded version of this one: https://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/ ?

Wow. It would certainly seem so. It would have been better if they posted that link instead of copying snippets and changing it be wrong.

@sql Programmers,

Again, welcome aboard but I have to tell you that some of the most valued human attributes on these forums are honesty and integrity, especially if you want to "build your brand". Rather than paraphrasing someone else's article as if it were your original thought, cite the article and add your thoughts. It's the right thing to do and, as you've just found out, the membership of this forum is large enough so that you'll be found out if you do otherwise.

And, if you are going to paraphrase, you should at least get it right. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 9

Are you using something like Spotlight to monitor server health, etc? If not, then tools like that can take you a long ways in "service improvement".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon