SQL Server DBA Tutorial 48-Update Statistics of All the Databases or Single Database in SQL Server

welcome to tech brothers in this video are going to learn how to update statistics also known as stats of all the databases or single database in sequel server in this demo we'll be learning updating stats of all users user databases using sequel server management studio graphic user interface and we'll be doing the same thing using T sequel script and we'll also be looking at different options updating indexed stats only updating column stats only using sequence entry management studio graphic user interface and T sequel so let's go ahead and do this I wanted to mention couple things that update stats can be performance hit so always if you wanted to update the status of your all database user databases should be off hours it's a really resource intensive it uses a temp DB a lot and then plus all the queries when you update the stats with the full scan at least all the queries needs to be recompiled and it takes time so it is a performance hit but if your normal updates that plan doesn't work default usually sequel server optimizer update the stats on regular basis depends on your query but if that's not good enough for you then you can go ahead and basically schedule your updates and once a week maybe is a good option for updating the stats on the database not every day certainly because it's a performance hit so just wanted to mention that before we get into doing this let's go ahead and this is my sequel server management studio right here I'm connected with sequel mirror is my instance and teeth Tech Brothers client right here TBS client is my sequel is my server where my sequel server sequel mirror is residing in order to will in order to create the update stats plan we'll be using the maintenance plan in sequel server and in order to go in maintenance plans who need to expand management of sequel server whatever the sequel server instance that you are connected with in maintenance plan right here you need to right click on maintenance plan and click on new maintenance plan and in this particular demo we're updating the stats of all user database so we're gonna go ahead update update stats all user DB this is the name of my maintenance plan you can name the maintenance plan on the naming convention that you have in your checklist we're gonna go ahead and click OK as soon as you click OK if this toolbar up here doesn't appear this toolbar is basically a container of a lot of tasks that you can use in order to build your maintenance plan but if it doesn't appear you can click on View and go on the tool bar right here and click and this toolbar should appear so as you notice right here there are different tasks available for us in order to build this maintenance plan we're going to go in this video we're going to go ahead and click on update statistics task and drag and drop right here in a working area on the right side so as you notice right here there's a big X sign that means that this task is not configured yet we have to configure the tasks before we can save and can use this maintenance plan so let's go ahead and configure this task in order to configure this - you need to either right click and go to the properties of this task or click on edit or you can simply double click on this task and it will open this updates task task window for you up here is a first connection local server connection this will be means that this is your local where you're creating right here TBS client secret mirror and in your case it would be different so that this would consider that if you wanted to use this as a central server that is managing other sequel servers you can also click on new and create connection and create on remote sequel server this maintenance plan so I'm gonna go ahead and go with local server up here we have database options and I mentioned in in the start of my video that you can select multiple options appear first is all databases that include the system databases if you don't want to use if don't want to update the stats on all databases including system databases then you can click on just system databases this will include master MSD be tempted be model or the other system databases that you you know that you have on your sequel server instance this will include that but basically most of the time we wanted to update the status of user databases which is this option right here all user databases this will exclude McMaster model MSD beat empty be and distribution database in case you have the replication setup so we're gonna go ahead and click on this in this demo but one thing I wanted to quickly show you that if you wanted to specifically define just few critical databases that you would like to go ahead and update the stats you can click on these databases and you can pick and choose let's say I wanted to update stats on report server I wanted to update stats on sales order database so we can go ahead and select in this way but keep in mind that up here ignore database where's state is not online this is very important let's say that you have attend some of the databases offline for some time and you're planning to bring them online after a couple weeks and but if you don't click this option you're going to get an error if you have in your database list right here any database that's a offline so I would always recommend that when if you're using maintenance plans to update your stats always click up here ignore database where the state is not online so this is for offline databases so we're gonna go ahead and click on all user database backup I'm sorry update stats so we're gonna go ahead and click OK as you can see that right here the object if you click on all user database that means index I'm sorry that means the tables and the views that's going to be update updated all the stats on tables and views are going to be updated if you wanted to do the column if you click on column state stats only it's going to do all the objects right here you get an option if you have more objects right here if you select a single database backup which I'll show you in a second that you will get option to select certain tables if you don't want to go ahead and update the stats on all the day the whole database or all the databases you can specify single database and single object or multiple objects you can select from there which I'll show you in a second and other thing right here I wanted to show you index stats only if you just wanted to update the indexes the stats then you can click on this option and you can update just the index index stats couple option right here scan type full scan and sample by if you have a particular sample of the data that you wanted to update the stats on you can do that this is useful in a certain case that your default stats are working fine for the whole database for every other application but there are certain queries who those queries are using some sample data and that's where you're running into issue and you would like to just go ahead and update the stats in that case so up here you can do the sample by 50 percent right here you get two options one is sample by percentage and sample by pros if you click on sample by rows that means this will depend on the rows of your table so it's gonna be zero to the maximum rows in your table and if you do the percentage is going to be 0 200 so sometimes it's a good idea just to get a certain query that is running slow and you do know that update stats is going to help you in that case you can go ahead and use sample data keep in mind this is also plays a big role in partition table so I just wanted to show you real quick so we're gonna go ahead in this demo full scan which is going to take some time and that's why I as I said that it's a performance hit you can you should always update the stats during off-hours and don't do the updates that's very frequent because if you do that your query needs to recompile which means a performance hit so just keep in mind so we're going to go ahead and go all existing statistics right here one good option using maintenance plans right here yeah if you click on view t sequel it will bring the T sequel script to update all the stats one advantage to do that if you click on this you can pick and choose what table in that perspective you wanted to update the stats and this is for ad-hoc you can also use this script in your sequel server agent job is let's say that you wanted to get most of the tables and views in the databases that you selected but there are certain tables that you really don't want to update the stats maybe they're really big or heavy or whatever the case may be you can go ahead and get the script copy the script and take those tables out from this script and run as a sequel server job using T sequel and we'll be doing that I'll I'll show you you know when I upload t sequel script and that basically that a sequel script is created by this view T sequel but keep in mind sometimes this VT sequel doesn't work exactly the way you would like to but you need to take a look and and go through the syntax if there is any syntax error you need to go ahead and resolve that so we're gonna go ahead and click OK as you can see right here our update stats stat task is configured and it's all green there is no red X up here so we're gonna go ahead and save all this plan keep in mind keep in mind that I have not scheduled the update stats plan I wanted to keep it on demand let's say that uh I run into some issues after three weeks and I know that my stats might have gone haywire and I wanted to go ahead and update my stats during off-hours I can go ahead and run the job then I this is my own personal feelings I mean if you wanted to go ahead and schedule your update stats you can click on here and you can go ahead and use this scheduler and you can schedule that but keep in mind the best practices from Microsoft see Microsoft and from your own experience as well so we're gonna go ahead and cancel this and cancel out from here as you couldn't write here if you you have two options in order to execute this if you go in maintenance plan refresh and you will see the mention maintenance plan right here you can right click on the maintenance plan and execute that maintenance plan or you yeah you can go in sequel server jobs because each maintenance plan when you save it creates a sequel server job if you don't see if your view is just like mine you can click on View and go to object Explorer detail and it'll show you all the jobs as you can see right here this update stats all user DB job is created you can right click on this job and start the job right here so it'll update your stats all the user database tasks will be updated now let's go back to our update stats maintenance plan and I wanted to show you a couple things so if you double click that will go that'll let you edit this mansion maintenance plan so we're gonna go ahead and use a single database which in this case I'm going to go ahead and use the port server so click OK as soon as you select a single user database right here you get an option whether you want it to do tables and views both or you just want it tables so if we just want a tables we can go ahead and just do the tables as soon as we select the tables you can go ahead and click on select one or more option this is what I was telling you that if you wanted just a specific table that you wanted to update the stash you can select it from here let's say that I wanted to update the stats on dbo employee and we're gonna go ahead and click OK and let's go ahead and generate our T sequel from here as you can see right here this is just going to update the stats using this database right here update statistics DB o dot employee with full scan right here is these are the options with the full scan we up here we selected that and you do have an option of sample I would say a thousand thousand hundred rows and let's take a look on the query right here with sample 100 rows I just wanted to show you that how you can generate the T sequel query and save it just in case if you wanted to just go ahead and run a query to update the stats on just particular one table or two tables you can add that you can click on go and add the other tables in this particular list so this is one other option that I wanted to show you so we're going to go ahead and cancel and we're gonna close this let's go ahead and upload T sequel script if I can find my T sequel script right here what we can create basically let me show you is real quick that's the same script that I have created so what we're going to do as soon as this script is generated this is actually a better option for you to learn it's gonna take a few seconds to generate the script for us so this is bit tedious right here you have to kind of keep going and select all the tables or the views or all the objects that you are interested in so we're gonna go ahead and close this new query and we can paste it right here I just want to go through a little bit with T sequel query right here so right here is the database this is going to be sales orders database right here all the way until it had it completes with the sales order database back behind the scene this is exactly what it is doing with full scan if you wanted to do with sample you can use with sample and then you can provide your sampling right here if it's a sample rows or a sample percentage right here so you can go ahead and run this script and it will update the stats of all the databases as you can see right here command updated successfully it has basically update all the stats on our sequel server user databases so just just to wrap up this video we have learned all the user database using sequel server management studio and also we have created T sequel script out of maintenance plan and we use that script and we ran basically that script we did not run the maintenance plan but maintenance plan back behind the scene the T sequel that was running we copied that and we ran that and ran successfully that kind of gave us assurance that is going to work our maintenance plan is going to work and we also looked at the options of update index status only and also looked at the options update columns stats only so it'll be your choice it depends on your performance what kind of scenarios that you're running in you can use either of that option and I hope this video helps

Channel: TechBrothersIT

Views: 33,116

Rating: 4.9111109 out of 5

Keywords: How to Update Statistics (stats) of All the Databases or Single Database in SQL Server, SQL, Server, Developer, TSQL, DBA, DBA Tips, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, transact sql, SQL Server 2014, SQL Server Advance Tutorial, SQL Server Tutorial for beginners, Tech Brothers, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Administration Tutorial Step by Step, Database (Software Genre)

Id: FtrRWaYnsyM

Length: 17min 23sec (1043 seconds)

Published: Mon Apr 06 2015