Why (still) use SQL Server Profiler?

this video is about sql server profiler and of course if you do not use sql server then this video is completely irrelevant for you sorry about that if you do use sql server and you don't know profiler and you also don't know extended events then this is probably a must watch for [Music] you [Music] hi i'm philip from codecabinet.com and as i said today i'm going to talk about sql server profiler this is a tool that too many access developers don't know but if you are not an access developer but uh are working with dot net and use an rm mapper like entity framework or and hibernate this video is probably of equal interest to you because you're dealing with the same problem there so sql server profiler is a tool that kind of profiles or visualizes all the sql statements that are sent to sql server and that is extremely helpful if you use some tool like an rm mapper or microsoft access that kind of translate the sequel for you or generate the sequel for you so you don't send or you don't write the the actual sequel that is sent to sql server and if you are an access developer you might be slightly shocked if i tell you you write sql in access and maybe something entirely different is sent to sql server with if you use odbc to connect to sql server so but before we go into more details i already mentioned extended events and if you um kind of heard about profiler and googled it and you will most likely found some articles texts or videos that tell you don't use profiler use extended events and there is quite a bit of truth in that extended events are the more modern way to trace and profile sql statements and for most purposes i actually would recommend using extended events because they have less overhead they they do not slow down the server so much because uh kind of logging and profiling all those sql statements that are sent to the server has quite a bit of overhead so if you do that on a production server and let it run for a longer period of time absolutely use extended events and also if you need more uh modern like uh events that have been added to recent versions of sql server you also need to use extended events because sql server profiler is basically deprecated it's uh included in every version of sql server it still works and it's still like in maintenance but it's not really updated with any new stuff it's basically uh i'm not sure if there were relevant changes since sql server 2008 or so or maybe even earlier so um it's not really bleeding edge technology quite the contrary but still there is one use case where i absolutely prefer to use profiler and that is actually the use case where i use like sql profiling the most and that's the reason why i would like to show it to you and introduce you to this tool so um yeah and the basic reason why i rather use it is because it's really to set up really easy to set up for that purpose and it's it's sufficient it it does what the situation requires and it's more user-friendly to to me the the ui shows the stuff that i want to know at that moment okay that being said let's over and this is the northwind database basically i moved it using ssma i moved it to sql server and all the tables are linked by odbc to the northwind database and obviously i don't i'm not really familiar with rm mappers so i'm going to do all examples in access but the basic core problem is the same if you work with vb.net and use an rm mapper so bear with me here and try to abstract that away if that is your field of work but if you're working with access then you're right right familiar with all the stuff so the tables are linked with odbc to a remote server on the local network so it's pretty fast i can query that stuff i can scroll here it's basically all it's instant so everything's fine isn't it like we we've got a couple of queries and i didn't really modify the queries they are just as they were in the northwind sample and let's look at the invoice data query that comes up pretty quickly all fine here i can scroll really great here now i want to create a new query and i get rid of this stuff and now i drag in the invoice data query and i want to see all columns but i want to see invoice data only for some products like where product id is between 70 and 77 and now let's run this and this is looking less good than before and well it's running and access is not responding and actually i know that access will not respond for quite a while so i kill access off say close the program and now let's turn to profiler so and as you see this is a profiler from the sql server 2016 but it does not matter at all as it's not changing at all at the moment so uh if you start it up you've got a gray screen with nothing in it and the most important things are the the the toolbar buttons up here and what you want to use is basically uh excuse me i get confused it's the new button and and we'll look at the other buttons in a while so we connect to sql server and i'm not one of the the persons who always work as sa as a system administrator i usually do not do that at all and i recommend you don't either but that's a bit of a gotcha here and another disadvantage of profiler if you want to run profiler you need to have system administrator permission on sql server and you will see in a second why that is the case so let's connect here and we get this dialog and you can use that to basically select a template but the standard one is good enough and we will go back to this dialogue in a second but i just run this and now you see there are already existing connections here and this is probably what you are seeing when you're connecting to a either production server or to a shared development server there's a lot of stuff going on here already and that is quite a nuisance because you got a lot of noise and we didn't even start our access application but we quickly can do that and because i terminated it it asks me so this connects and we can say login and it's doing stuff and we switch over to profiler and now let's pause this and we are not looking for the actual sql stuff and you see this is microsoft let's do zoom in a little bit or rather with active zoom the application name is microsoft office that's pretty obvious and the login i use is northwind user and that is what i am interested in right now and you see there's a lot of stuff in here with a different user and if we go up here there's another user doing stuff here that is running on a different computer and a loop to generate the noise you would normally see if you connect to any server that's used by someone else at the same time and you might even see that if you have sql server management studio just open on your computer connected to the same server you will also see all sorts of commands going on there so this is quite realistic even if it's just your local server used by no one else and to filter down this noise we need to go back to the properties and before we do that uh let's quickly look at the the buttons here again we've got these are the most important buttons here these ones and this one we're going and this is basically play to start the stuff a pause to to pause it and stop to to stop your trace and the eraser thingy on the left is to clear everything that's in here and finally this one is the typical properties icon and that's what we are going to need now but before we can use the properties really they would be read only now before we can change properties we need to stop the trace and now we can go we can use the clear button to get rid of all that stuff and now this is the same dialogue you saw at the beginning and the important parts is in the event selection tab and you can select lots of additional events if i click on the show all events button here you see there are lots and lots of events in here and you can expand them and and they're grouped and has lots and lots of stuff in there but usually um you don't need that for the purpose of looking at sql statements that are sent to the server and that's what we are going to focus on today so i'm going to going to break out of my stuff here so sorry about that and i hit escape so the dialogue closed so here we are again and i'm not going to use show all events and there are also lots of other columns here and you usually don't need them either so i need that but what is really important is the column filters dialog and you got a couple of different stuff in here to filter basically columns and the most useful is usually to filter the noise down is the login name or the nt username so i'm going for northwind here and it's a like operator so i have to use the percent sign at the end or i would need to to write the full name but i'm lazy so i just um enter that to filter it down and hit ok and say once again run and now this looks much more like i want to see it but um that's important if you work with access and i believe it's the same with most orm mappers microsoft access uses multiple connections and it opens additional connections whenever it feels like it or rather when when it thinks it needs another connection and i think some orm mappers are doing the same that they're using multiple connections at the same time so you cannot just filter on one spit on on one process id on sql server but you need to filter about the username or the application name or maybe the local client name but for our purpose here the username is just fine you see all the noise is basically gone so these are existing connections and we're not really interested in these so i clear that but the trace is still running and now we go over to access we close this and go back to our invoice data query and open that in design view and you see this is a query involving six different tables or rather five tables and one other query but that's not really the the matter here um so you would expect to see a sequel statement pretty similar to this nasty beast here and the important part you don't need to really read it but the important part is that all six tables are listed here in the from clause and you would expect the sequel that is executed to sql server or send to sql server execution you would expect that to be roughly the same but it isn't so we we clear this once again and now we run this query and now let's hit pause here and go over to sql server and you see it's not like sql server got one sql statement but it got a lot and you see it down here 207 rows a lot so but let's go up to the top and there you see the the statement and that is basically the main statement here and i know it's hard to read so let's copy that to like something like notepad to better visualize it and to you switch to sql language quickly and now the important part to look at is the from clause here and you might be shocked there are only one two tables here not five not six as in axis only two so let's go back to profiler to to look what's happened here so this is on the orders table and the shippers table and now the next statement is another statement on the employees table here then there is another statement on order details and yet another statement on customers and one more on products and if we go to our access database and just go back to the design view you see orders and shippers these are the two tables um that that we had here initially orders and shippers these were joined in one in one um statement on the server and then the the other tables here the customers the employees products and order details these were all added later in separate queries and that is quite shocking because um it's not a single sequel but multiple statements if we go back to to sql uh profiler and look at the statements let's look at this one and the important bit is this here the where condition it's basically doing a key lookup here and uh from from the database perspective of the jet engine who received our query and translated it to this mess it's not really that bad to do that because there is a chance that sql server would have processed the query in the same way it would have just gone to to the core tables grabbed all the records and then they're doing key lookups for all the other tables to fetch the records from those tables it's possibly that sql server would have done it the same so the basic idea the jet engine had here to to translate it to these queries wasn't too bad but the problem is if sql server does it or the jet engine with a local access database then this is a decent approach it might not be the best but we don't know that it's reasonable to to work that way but if you send all the key lookups over the network to fetch the data that is going to be a bit of a problem and this is on a local network already if you move to the cloud and put your database in azure sql or any other sql server on the cloud and have latency between your local client and the remote sql server over the internet then this is going to kill performance even more than it did already here so and and it's all the same with the other queries that are key lookups and they are not ideal so but look uh further down here now this is also we didn't do anything else then run the single query it's all the the microsoft office application with the northwind user and you see these sp execute statements and you're going to see that a lot if you're using odbc to connect to sql server with access and the the thing is you need to look at these statements to figure out what um there's it's a prepared statement and the first value here like here the the three or the one up here we got another one this is the idea of the statement and if you want to find out what access sent to the server you need to go up all the way up to find a matching prep exec and that is actually what we were looking at at the beginning here so and this is the important bit here got a parameter p1 and it's equal three and then the the prep exec is called it's preparing a statement and the bit um at the back is the statement the select stuff over here and it's passing in the p1 as the statement identifier and that is the value three so whenever you see um whenever you see the the three down here like this statement the first value is the three so it's statement three from up here i really need to practice a little bit um with with the zooming stuff in here so you know this statement is basically a select id from employees table so or rather no no no i i made it wrong because um the the basic explanation was correct but here is another prep access statement in between reusing the statement id3 so this is rather a select id from company but but the basic the basic stuff remains here you need to go up like if you see the three here you need to move up to the next prep x prep exact that uses the three an id and then you see what that statement actually is it's a little bit complicated but it actually makes sense performance wise to kind of prepare a statement like compile it and then only reference that prepared statement that improves performance quite a lot and if you look at this there are lots and lots and lots of statements in here that access is sending to the server and that is obviously not really ideal to to do here and it's getting um getting worse that was the initial execution of the query and now actually here access is starting to really fetch data and then it's usually doing in batches of 10 rows so it's it's first executing the query getting the id values it needs and then it's fetching the actual rows there's a lot of indirection and a lot of chattiness between sql server and access or rather access and sql server because it's access initiating all that stuff and i'm not going into more detail how you can influence that and there's not too much you can do about it but a little but that would go totally beyond that and i guess you you will see similar stuff maybe not as bad but similar stuff when you use an orm mapper like entity framework so what would be an approach to rein in this problem well the problem is that access just kind of splits this query into multiple pieces and and kind of sends off all sorts of sql statements to the server and the core method to prevent that is to either use a path through query where you send sql directly to sql server and then the local jet engine the xsas engine will not mingle with your sql it will get straight to the sql server but the drawback is path through queries are read only so what i usually do is kind of prepare these statements these sql select queries with multiple tables and i create a view and uh and i'm not going to show it but i prepared a view invoice data and that is basically this query on sql server and it also takes a little bit of time but um if we go back to our original performance problem and we create a new query and now pull in the invoice data view which resides on sql server and that's linked to the local database and do the same as before like uh adding the star going down to product id and say it should be between 70 and 77 and make a guess how long it will take now not long and you see only three rows were returned by that query and just just to show the sheer horror let's do it again with the original query create a new query put in the invoice data here put in the star here uh product id between 70 and 77 before we execute that we switch over to profiler and just clear that and start the profiling again and now we run this and now you see on the left hand side maybe we will make this wider it's not responding and it's of course it's not responding to to stuff uh to drawing the window but you see it's running it's still running and we are at 35 000 rows in profiler that's 35 000 queries sent to sql server by 45 000 now and i do not know how long it will take of course this northwind database is a little bit bigger than the the plain sample you can download there are one million rows or one and a half million rows in the orders table and and more in the order details and stuff so it's a pretty big um pretty big query that is running in pretty big data but you now see for this particular query it's absolute disaster what the ace engine did to the query with all the key lookups because to find the three rows that we are actually looking for it has to fetch first the the one and a half million orders then it has to do the key lookups for the detail rows to get to the product and to compare the product id or at least to the order details and you see it's at 110 000 rows now and yeah that is basically completely insane here and just for for the sake of it um let's compare that let's pause this here and yeah i need to kill off access because it's not going to respond at all for a while but just for the sake of it i should have shown that earlier let's compare that to the approach with a view so we are logged in we create a new query we just pull in this thing here and do once more the query for product id between 70 and 77 and make sure we run profiler i clear everything it's still running and now i run the new query and it's done pause it you see it has sent over one query down here on my view invoice data and it's it's just one query there are two rows because one is batch starting and one is batch completing so i wasn't exactly correct as i said there were like hundred and ten thousand queries sent to the server because it was only uh significantly less like half of that because we get here one row for starting a query and one for completing a query but nevertheless it's an insane amount of of stuff here but nevertheless access still is using these key lookups to fetch the values that that it's still doing here but the original query is now really down to one single sql statement because jet engine doesn't know need to split that up anymore yeah so i think that could have been a shocking revelation to you if you have never done that before and that is something that that is really really helpful to to know if you run into performance issues with sql server backends that you actually have a tool to look behind the scenes and actually see what access is doing with sql server and that is if you just if you are developing something you write a query in access against the the sql server backend and you hit execute in the query and you see it's blocking everything or even if you are one level above in the ui and you create a form and you worked on the form and think yeah it looks nice now and then you open it in normal form view that all the the queries are executed for the um for the form record source and the row sources of the drop downs or list boxes and maybe the the sub forms you might have in there and then everything freezes and access doesn't do anything or it's taking much longer than you expected and you would normally be totally lost what's causing the problem and would need to kind of switch around sql statements and trial and error to find the problem but with the tool like profiler that you can just fire up uh just start maybe enter a filter to filter to your username to get rid of any noise that's in there additionally but in a couple of clicks you can look behind the scenes and that is where i always at the moment at least i always would prefer profiler over extended events because the user interface for extended events is not as straightforward it's well uh i'm not going into any more details of extended events i'm but just keep in mind i'm not saying profiler is better than extended events because it's not the case but for this particular purpose on hitting a button to look behind the scenes and what's going on in sql server on your development machine right now profiler is still the better tool for this purpose so okay um i think that's it for today thank you very much for watching i hope you enjoyed the video and i see you next time you

Channel: codekabinett.com/en

Views: 1,947

Keywords: SQL Server, Access, ODBC, ORM, Entity Framework, Nhibernate, Profiler, Prepared Statement, SQL, Statement, Performance, SQL Statement

Id: ZyXWEvayTok

Length: 35min 17sec (2117 seconds)

Published: Sun Jan 24 2021