February 12, 2016 at 1:18 am
Hello
i have assigned to do some DBA TASKS in order to improve the funvtionality and the performance of the SQL Server.
some of the tasks that i have to do are:
1. DEFRAG/REBUILD INDEXES
2. DROP INDEXES (unnecessary)
3. CRATE INDEXES (Suggest from system)
4. BACKUP/restore
5 CPU Monitoring /RAM/DISK
6. ANY OTHER SUGGESTION FOR BETTER PERFORMANCE OF DATABASE
could you help me to find any script/stored procedure/guide in order to do all these tasks
i will appreciate also if you suggest any other tasks for better performance
thanks ina dvance
February 12, 2016 at 3:31 am
I use the Ola Hallegren scripts for Index/Statistics, Integrity Checks and Backups.
February 12, 2016 at 4:17 am
georgheretis (2/12/2016)
Helloi have assigned to do some DBA TASKS in order to improve the funvtionality and the performance of the SQL Server.
some of the tasks that i have to do are:
1. DEFRAG/REBUILD INDEXES
2. DROP INDEXES (unnecessary)
3. CRATE INDEXES (Suggest from system)
4. BACKUP/restore
5 CPU Monitoring /RAM/DISK
6. ANY OTHER SUGGESTION FOR BETTER PERFORMANCE OF DATABASE
could you help me to find any script/stored procedure/guide in order to do all these tasks
i will appreciate also if you suggest any other tasks for better performance
thanks ina dvance
You can search for Glenn Berry's Diagnostics Information Queries for SQL Server... It's a bunch of very useful DMV queries that monitor a lot of stuff on SQL Server.
1. DEFRAG/REBUILD INDEXES
- Can create a maintenance task on SQL Server (it does this out of the box)
2. DROP INDEXES (unnecessary)
- The DMV queries give you this information... but you can have one index that has 1.000.000 writes and no reads.... You drop it, it's not used... Then one day the CEO decides to get that useful report from the database and it takes him forever to get the report.... That was when THAT index was used... BE VERY, VERY CAREFUL ABOUT DROPPING INDEXES!!!
3. CRATE INDEXES (Suggest from system)
- Once again the DMV's give you this... But if an index is built on a table all the DMV's index information is reset. Also look very carefully at the results... Some times SQL Server suggests and index on table A with col1, col2 and include col4 and also another index with col1, col2, col4 and include col3, col5... These are not the same but are not very different... Also col1, col2 is not the same as col2, col1... If both columns are used they are the same, but if only one is used they are completely different... There are some great posts, some from Gail Shaw, if you should use a lot of small indexes or few large ones...
4. BACKUP/restore
- Can create a maintenance task on SQL Server (it does this out of the box), but be careful since most people worry about backups... It's wrong.. Worry about restore.. It's the downtime you have to be worried about...
5 CPU Monitoring /RAM/DISK
- The DMV queries have this also and there are some other posts online about using Resource Monitor and other tools.
6. ANY OTHER SUGGESTION FOR BETTER PERFORMANCE OF DATABASE
- CHECK THE QUERIES USED!! No miracles can be accomplished by SQL Server, not even with 1TB RAM and RAID10 SSD Disks, if the queries are poorly written..
- Use DBCC CHECKDB before you perform a backup.. If the database is corrupt also the backup will be!!
February 12, 2016 at 4:31 am
Assuming that list is also your priority order, I'd change it.
First and foremost, get the backups set up. Most importantly here, establish, with the help of the business, what your Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are. Basically, you need to know how much data the business is prepared to lose (and zero is actually not an acceptable answer, some degree of data loss is always possible). This will help you figure out how often you need to run full backups, differentials and log backups. I have a full article to get you started on backups here[/url]. It has a number of scripts.
After that, I'd focus on getting monitoring set up. First, get a process that will alert you if the server goes down. Then, a process that will alert you if there are error conditions. Then, you can focus on gathering performance and behavior metrics. Unfortunately, this is either lots and lots of work, or, you should invest in a monitoring tool to do it for you. I strongly recommend getting the tool. There are a number to choose from, but I think for entry-level work, our software, SQL Monitor is best[/url].
After that, you can start to focus on statistics maintenance. Then worry about index maintenance. For those, I'd suggest you take a look at Minion Reindex [/url]by MinionWare. It's a free piece of software.
Also, it'd be a good idea to ensure that your server is set up correctly and that you've adjusted SQL Server itself. I have a presentation I do on this. Here are the slides.
That ought to be enough to get you started. Focus on the backups first. The most important aspect of the job is the ability to restore a database, but you can't do a restore without the backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2016 at 4:54 am
PiMané (2/12/2016)
1. DEFRAG/REBUILD INDEXES- Can create a maintenance task on SQL Server (it does this out of the box)
Please don't. The maintenance task is braindead. Use Ola's scripts, use Minion reindex, use any of the other excellent rebuild scripts available over the maintenance task.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2016 at 5:44 am
EXEC sp_MSforeachdb 'USE [?];
DECLARE @query NVARCHAR(MAX) = '''';
SELECT
@query = ''ALTER INDEX ['' + i.name + ''] ON ['' + OBJECT_SCHEMA_NAME(ps.object_id) + ''].['' +OBJECT_NAME(ps.OBJECT_ID) + ''] REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); '' + @query
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N''LIMITED'') AS ps INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID() AND avg_fragmentation_in_percent > 10 AND page_count > 50 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
EXEC sp_executesql @query'
EXEC sp_MSforeachdb 'USE [?];
DECLARE @query NVARCHAR(MAX) = '''';
SELECT
@query = ''ALTER INDEX ['' + i.name + ''] ON ['' + OBJECT_SCHEMA_NAME(ps.object_id) + ''].['' +OBJECT_NAME(ps.OBJECT_ID) + ''] REORGANIZE; '' + @query
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N''LIMITED'') AS ps INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID() AND avg_fragmentation_in_percent <= 10 AND page_count > 50 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
EXEC sp_executesql @query'
You can use this script for rebuilding/reorganizing indexes...Change the parameters according to your table's needs (PAD_INDEX, FILL_FACTOR, ...). And you can also change the page_count parameter to a larger value to avoid small tables... and ps.index_id > 0 so it won't rebuild/reorganize HEAPs... Just don't do it!
The script runs for all databases....
Just another thing: rebuild indexes will enlarge your database.... DO NOT RUN SHRINK AFTER REBUILDING INDEXES!!! it will create fragmentation all over again!
February 12, 2016 at 8:58 pm
Having just been through a bit of a reindexing nightmare, I urge extreme caution. Non-clustered indexes are a bit of a pain in that they usually not in the same order as inserts. Even with a fairly high cardinality, if your FILL FACTORs aren't set right (most folks have them set at 0 or 100, same-o-same-o), you can cause huge slowdowns of your app just by reindexing. Fragmented indexes became that way because of page splits, which make room in the indexes for INSERTs. Removing that room will cause page splits when new INSERTs occur, which slows down the app, sometimes causing some pretty serious blocking until enough pages have been split.
Before you use any reindexing method, check for the percent of page fullness as well as the fragmentation. That can give you some very good hints as to which indexes need a FILL FACTOR of other than 0/100 and then rebuild the indexes with the correct FILL FACTOR. You also need to consider the average row size in deciding what the FILL FACTOR should be. For example, if a single row takes 7000 bytes, then there's no use in changing the FILL FACTOR. If each row is tiny, then setting a FILL FACTOR of 80% might be serious overkill depending on how many rows are typically added to the table.
Of course, there's a trade off. Decreasing the FILL FACTOR inherently reduces performance of SELECTs because it has to read more pages to read the same number of rows unless the SELECTs are typically single row SELECTs, in which case fragmentation doesn't actually matter. And, of course, you have to consider any batch code you might run on nightly jobs as well as reporting code, all of which just love a nicely defragmented, low free space index.
Keeping stats up to date is a whole 'nuther story and is sometimes more important than defragging especially if you have an ever increasing clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2016 at 2:43 pm
I'm with Grant. Get backups going first, and test restores. Make sure you can actually recover the db.
My second task is usually to look at security and assert some control over the database. This shouldn't take too long, nor should it be onerous, but I've had too many occasions where I start to improve performance and then someone changes something to make it worse. I'm all for changes, but I'd like to know what's changing so that I can understand the
Get monitoring set up. Baselining is a way to do this. A few items for you:
- http://www.sqlservercentral.com/articles/132034/
- http://www.sqlservercentral.com/articles/94656/
- If you don't want to spend time managing/building software, SQL Monitor - http://www.red-gate.com/products/dba/sql-monitor/?gclid=CjwKEAiAxfu1BRDF2cfnoPyB9jESJADF-MdJq8K3fH_ol7V7n9lO1fm5oJ_xU6wopGwkmhd7XRE0CxoCvnLw_wcB (Disclosure, I work for Redgate)
Then go for indexes. If you don't know how things are performing, changing indexes isn't necessarily a good first step. As Grant/Gail mentioned, use Ola's scripts or Minion Reindex. Then as you learn more, look through Jeff's advice.
February 13, 2016 at 5:22 pm
I'll third that... Backups and test restores first. Security next and very soon... hopefully, the same day.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2016 at 5:51 pm
Add me to that list. Backups should always be first. Then security - hopefully disable the sa login. If not, work to get it done.
The indexing and statistics are each pretty big topics. There's a lot of research and learning to do. My advice would be to not try to write your own until you get through your list. In the interim, go with something that's already built. Whatever you do, listen to Gail and don't use the maintenance plan.
You've got your work cut out for you. The good news is that you'll learn a lot going through them.
Edit: I use SQL Sentry for monitoring my instance. It isn't free, but it does a good job. There are other products on the market, including one from Red Gate.
If you have to get these things done quickly and don't have the time to spend learning all this stuff, you might be better off hiring a consultant to help you get things under control. There are a several on this site. They certainly wouldn't be free, but very likely worth it.
February 14, 2016 at 12:20 am
Thanks for the advice
Due ro the fact that u have to do the Dba tasks by my own, i have ro use free scripts and stored procedures for all the tasks.. So i appreciate any advice about scripts or sp or free tools for the specific jobs
Thanks in advance
February 14, 2016 at 1:25 am
Everything listed in this thread (other than the two tools Ed mentioned) are free.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 4:39 pm
If you are not using enterprise edition, I think it would be ok with rebuild indexes weekly using maintenance plans
February 15, 2016 at 4:49 pm
ramana3327 (2/15/2016)
If you are not using enterprise edition, I think it would be ok with rebuild indexes weekly using maintenance plans
Maintenance plans will rebuild ALL the indexes... even the ones that don't need it. That's pretty expensive especially in the FULL Recovery Model.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2016 at 6:30 pm
Jeff Moden (2/15/2016)
ramana3327 (2/15/2016)
If you are not using enterprise edition, I think it would be ok with rebuild indexes weekly using maintenance plansMaintenance plans will rebuild ALL the indexes... even the ones that don't need it. That's pretty expensive especially in the FULL Recovery Model.
Yes it is. There's always Ola's scripts or Minion reindex for free tools here. I wouldn't use the maintenance plan.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply