Does your server look like this?
Many of us have inherited a SQL Server instance that has all SQL Services installed. Someone, maybe even you, went through the SQL Server installation process using GUI and checked every option available to them, then just clicked Next, Next, Next and then Install. If this is your environment, please take a moment to evaluate and decide which of these services that are required.
From a performance tuning perspective, it is important to only run the services that you need. Each of these services can consume resources on your server. Sharing resources reduces what you SQL Server Engine, SQL Server (MSSQLSERVER) or named instance SQL Server (ServerName\NamedInstance) can consume to run your workload efficiently. It is highly recommended that all other services not associated with the engine be run on a separate server.
Sometimes due to licensing concerns, this not a viable solution, and that is understandable. However be sure you are knowledgeable about how each configuration setting, like memory, that may need to be adjusted with other services sharing these resources. As I stated in a prior blog the max memory set inside SQL Server does not impact other services like SSIS, SSAS or SSRS. Those services take their memory from the operating system allocation, which in turn can take memory from the SQL Server engine. Be sure to allocate enough to each service. (Note: you may end up needing to spend more in RAM, than licensing, especially if you can run standard edition for BI tools like SSRS or SSIS).
Take a moment and look to see what services you are running versus what you are using. If you find that SSIS, for example, is running and you are not actually using it in your environment turn off the services. Take the time to set it to DISABLED and not just STOP the service. Stopping the service isn’t enough if it is set to start automatically. When the server is rebooted, the service will diligently start, and you do not want that. Make sure to disable the service to prevent that from occurring.
Contact the Author | Contact DCAC
Bob Pusateri (B|T) tweeted a quote image that really struck a chord with me and elicited a strong reaction from myself. It got me thinking I need to write a blog on this, so here we go.
Many times, over the years I’ve had conversations with people in which they have said things like below.
“I can’t learn anything new because my work won’t send me to training”
“I can’t learn about xyz because my boss or coworkers won’t sit down with me to show me how”
“I don’t have time to learn anything new”
“We’ll never go to the cloud, so I don’t need to know that”
“I’m too busy just putting out fires, I can’t leave the office to go to training”
“I can’t learn anything new because my work won’t pay for training”
“My free time is my own. When I’m not at work, I don’t want to think about databases”
“I’m too burned out, to learn anything new”
“It’s just not a priority for me right now”
If you can hear yourself saying any of these, I want you to stop right here and reread the image above several times. If you make excuses like these, you will get nowhere fast and likely become disgruntled which can lead to feeling trapped. If you wish to have a career and not just a 9-5 job it is critical you invest in your own training.
Now many of us don’t have the money to pay for things like week-long conferences or expensive hands-on training, I get that. There are other ways to get training, it’s not necessary to set your target on those. Start small. I challenge you to read one blog a day, that’s it. By doing that simple thing you vastly increase your knowledge. I’ll list a few of my favorites at the end of the blog.
Next, be sure to attend your local user groups, Code Camps, or SQL Saturdays. These are usually free to attend in-person training opportunities on various topics, not to mention it’s your chance to network with other technology professionals. At these events, talk and engage people, as you never know when one of those conversations may lead to your next career opportunity. That’s a win-win. An added benefit is that usually these types of events provide you with motivation to further your own professional development. You’ll be surprised on how they will affect your outlook on work and learning. If you can’t make it out of the office or home for training, did you know there are many virtual training opportunities available like PASS’s virtual user groups ? These are live training sessions, much like those at SQL Saturdays, given during lunch hours or after work. They are also often recorded so you can watch them when its more convenient.
Lastly, do yourself a favor and join Twitter. So many of us in the SQL Community are out there learning from each other every day. Bloggers tend to leave breadcrumbs for learning.
We post links to our newest blogs.
We note things like Currently Reading: xyz Blog Topic with a link to that blog.
Follow the #SQLHELP hashtag. We are all out there helping each other trouble shoot issues and providing references to answers. This is a fantastic place not only to find help, but to learn.
These are just a very few things you can easily do to start investing in yourself and your training. Below I have listed sites for free or low-cost training, links to great blogs you may want to start with. However, you start… the point is to START. Stop complaining and making excuses and just go for it.
I started out working at the Port of Virginia with ZERO SQL Server Database Admin experience straight out of college. They sent me to a SQL Server bootcamp, (the only training I EVER got from them in 12 years) in which after, I took my certification exams and I was off and running with no more training. As we know, when you attend training like that, it’s really just a ton of topics thrown at you, an exam is taken, and nothing is really digested. In order to be successful and keep the Port’s databases running as their only DBA, I had to invest in myself and my own training. If it wasn’t for me taking the time to self-train I would not be where I am today and my tenure at the Port would have been disastrous.
SQLPass – pass.org
SSWUG – https://www.sswug.org/
LinkedIn Learning- https://www.linkedin.com/learning/
Microsoft Virtual Academy – https://www.microsoft.com/en-us/learning/training.aspx
MS SQL Tips – MSSQLTips.com
SQL Server Central- https://www.sqlservercentral.com/ (stairways series are good for beginners)
Denny Cherry and Associates Consulting- https://www.dcac.com/publications/blog
SQL Skills- https://www.sqlskills.com/sql-server-training/online-training/
MS SQL Tips- https://www.mssqltips.com/
Simple Talk- https://www.red-gate.com/simple-talk/
SQL Performance- https://sqlperformance.com/
C-SharpCorner – https://www.c-sharpcorner.com/
Each day I learn something new, I take the time to invest in my knowledge and career. No one has to tell me to do this. I want more out of a job, I want a career, so I make one. As the quote above say, I am unstoppable, there is no reason you can’t be too. I understand that this is a personal choice and it is perfectly fine not to want this, but don’t complain while doing nothing. While there are exceptions to the rule, most companies don’t care about your career trajectory. HR is there to protect the company from lawsuits, not to help you. You are the only one responsible for managing your skills and career, and if your company isn’t investing in your training, you need to do it yourself.
Contact the Author | Contact DCAC
Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as part of your index maintenance procedures that you periodically check to see if your indexes are being used. Many times, indexes are created in the belief they are needed but in fact they are never used. You can reduce that IO overhead on inserts when you remove unnecessary indexes.
How Do You Find Them?
Glenn Berry (B|T) has a fantastic script as part of his diagnostic scripts (link) that helps identify index utilization. In his diagnostic scripts it is listed as Script # 53.
— Possible Bad NC Indexes (writes > reads) (Query 53) (Bad NC Indexes)
SELECT d.name,OBJECT_NAME(s.[object_id]) AS [Table Name],
i.name AS [Index Name], i.index_id,i.is_disabled, i.is_hypothetical,
i.has_filter, i.fill_factor,user_updates AS [Total Writes],
user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
'USE ' + QUOTENAME(d.name) + '; ALTER INDEX ' + QUOTENAME(i.name) +' ON ' +OBJECT_NAME(s.[object_id]) +' DISABLE '; as statement
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
join sys.databases d on d.database_id=s.database_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
and i.name not like 'PK%'
and i.name not like 'f%'
ORDER BY [Difference] DESC,
[Total Writes] DESC,
[Total Reads] AS C OPTION (RECOMPILE);
Things to Consider in the Results
Simply WRITES> READS.
If you are writing to your index and not or infrequently reading (Seeks and Scans) from it, you may want to consider disabling then dropping. It takes a lot of resources to update indexes, store and maintain them, don’t waste resources when you don’t need to. Also, it’s important to note that these numbers are only as good as your last SQL Server service restart. Note the index usage statistics can also be reset if you are running SQL Server 2012 version below SP2+CU11 or SP3+CU3, by executing an ALTER INDEX REBUILD of that index. For SQL Server 2014 until SP2 the ALTER INDEX REBUILD of that index also applies.
You can see in the screen shot that there are five indexes that are potential candidates for removal. We know this because the Total Writes column is significantly higher than the Total Reads. It’s just that simple. What is great about this script is that it combines user seeks, scans and lookups which makes it easy to see total reads. It also dynamicaly creates a disable script for you to use, if you choose to disable them.
I highly recommend you run this in your environment to see how your indexes are doing. When it comes to performance tuning, fixing wasted resources can make all the difference. But be careful we all have indexes that are only used during budget season or once a year reporting. I highly recommend scripting off all indexes before dropping them.
Contact the Author | Contact DCAC
One thing I learned while working as a database administrator over 17 years is the importance of teamwork across departments. Many times, we have to rely on network and SAN administrator to make changes to their environments in order to make SQL Server run more efficiently. There are times where the storage and network create bottlenecks for SQL server and after doing all the possible tuning you can do with your code and configuration you must turn to them for modifications to their hardware. Knowing how to effectively work with other departments without placing blame is crucial.
As most do, we take our work personally. After all it’s your hard work, decisions and efforts that make the environment run as it does. Telling network admins and SAN admins their environment is running poorly or misconfigured can cause a very adverse reaction. At times it can even cause them to refuse to work with you or make an effort to help fix the issue. I often refer to this as, telling them their babies are ugly. You need to learn how to nicely do this without pointing out the flaws in their genetics.
Checkout how I deal with Ugly Babies in my latest editorial on Database Weekly or use this link to read it on SQL Server Central.
Contact the Author | Contact DCAC