Bill Ramos
SQL Down Under Show 46 - Guest: Bill Ramos - Published: 2 Jan 2010
This show features recent SQL Server product group member Bill Ramos discussing management and monitoring of SQL Server, his new role at Advaiya, and SQL Server extensibility.
Details About Our Guest
Bill Ramos is involved in the SQL Server product group but now Bill is part of Adviya.com
Show Notes And Links
SSMS Tools Pack http://www.ssmstoolspack.com/
Show Transcript
Greg Low: Introducing Show 46 with guest Bill Ramos.
Welcome. Our guest today is Bill Ramos. For a long, long time I have known Bill involved in the SQL Server product group but now Bill is part of Adviya.com. So with little introduction I will get Bill to tell us. Hello Bill, tell us about your new role.
Bill Ramos: Hi Greg. I am currently what they will call a work stream manager for Adviya and responsible for the SQL Server product line. In my new role at Adviya, I am involved with helping the Microsoft marketing team of all folks come up with marketing strategies around IT to Business infrastructure optimized strategies, marketing and sales messaging frameworks for the SQL Server product. In addition one of the things I do is work on mini projects and some of them can be rather big such as for SQL Server Migration Assistant I am putting together some white papers around MySQL, access to SQL Server and access to Azure as well. As part of that content, we also do some development of things like click through, digital marketing, streaming media and things even like this podcast we can record for the team.
Greg Low: Yes, cool. What I will get you to do first up though of course for people who may or may not know come across you in the past is how did you ever come to get involved in SQL Server in the first place?
Bill Ramos: OK, well. As I mentioned before there is a song that I think positioned it well and see how it comes across.
Song: Long, long time ago.
Greg Low: Don McLean. Yes indeed.
Song: Remember the music makes me smile.
Bill Ramos: OK.
Greg Low: Lovely.
Bill Ramos: So long, long time ago in 1992 I think it was, I got involved with this thing called the Ashton Tate Microsoft SQL Server for OS 2.
Greg Low: Yes.
Bill Ramos: And one of the things I did was actually poured a set of this is actually when I was at Ashton Tate back then, before Borland came out and I worked on a set of test suites, performance test suites based on the old SDT accounting systems that ran workloads on the server just to make sure it performed Ok. So I was doing this dBase to SQL Server conversion way back then and then Borland well they didn’t quite carry that product line ahead, but I was still kind of involved with how dBase for Windows would actually hook up to SQL Server through what was the Borland database engine.
Greg Low: Yes the BDE. Yes I recall that.
Bill Ramos: Yes the BDE, and then in 1995 I joined Microsoft. Initially to write a set of database designers that would replace the Access designers that they shipped with Access 1.0.
Where we were going to do new database designer, table designer to replace what you still see today in Access and actually you continue to see Access.
Greg Low: Yes indeed.
Bill Ramos: Yes and then back when Bill G did his infamous internet memo we flipped all our code that was going to against Jet to focus on SQL Server and so back in 1996 we started to heavily get involved with getting those tools to work on top of SQL Server and eventually ship them as part of the kind of the beginning of SQL 7.0. We made sure that the tools would work on SQL 6.5 and part of the Visual Studio 97 release.
Greg Low: Yes I certainly remember the period where suddenly. Must have been the period where he sent the infamous memo around but I suddenly recall that he suddenly like discovered the internet or something and I recall all of the tools suddenly become very internet enabled, almost overnight. Often in ways that were quite bizarre really and I look at the time when SQL Server 6.5. I remember of course we had Visual Basic 6.0 at the time, and a lot of the things they added into that are things like some of the web classes and all those sorts of things and again of course were things that were later removed. They seemed to be fairly ill conceived at the time. In fact I had a friend at the time said you only buy the odd numbered versions and I think he was pretty well right actually because I noticed that almost everything added in 6.0 was later removed but anyway.
Bill Ramos: Well let’s not forget the infamous Visual Interdev version 1.0.
Greg Low: Yes, yes, yes, yes.
Bill Ramos: With the chill pepper application that I had a lot of fun with when we took the show on the road.
Greg Low: Oh that’s good, and so in recent years though you have been involved the tools area?
Bill Ramos: Well, so let’s see, I did my stint in Visual Studio doing the database designers. Spent one year in VB 7.0 before it became VB.Net doing some project management for that release and then saw a good opportunity over in the world of Access to try and integrate SQL Server with Access at that time. That was at the tail end of the 2000 release and we then did the big push for the ADPs again with Access XP and then 2003. Right after the 2003 release, I was looking at how to integrate .Net programming language to replace VBA. Needless to say that never went anywhere and so a friend of mine who I worked with back in the early days of Visual Studio who happened to be working on the manageability tools at that time said ‘hey Bill how would you like to come work on Management Studio?’ We are only about 6 months from shipping and this was about 2004, early 2004, for the 2005 release.
Greg Low: For the release, yes.
Bill Ramos: So I said ‘ Yes sure why not.’ So since basically 2004 I have been working on Management Studio for the tail end of the 2005 release and then for the Katmire release I led a team that helped product Policy Based Management, the Data Collector Management Data Warehouse functionality that shipped with Katmire.
Greg Low: In fact we will probably spend a fair bit of time talking about Collection and things but first up maybe a quick question on just your feeling on the overall state of Management Studio at the moment as to, there is good and bad things and wanted to get what your overall feeling is?
Bill Ramos: Well, Management Studio I think for 2008 and 2008R2 are pretty rock solid. 2008R2 started to bring in some of the Development features. I think Snippets got in there, check it out but I think the cool thing we added there was the integration and the ability to connect to SQL Azure. So that you can start to use Management Studio against SQL Azure in writing queries and keeping track of both your on premise and off premise databases. Starting with cumulative update 3.0 of, I think of R2 you can now actually run reports inside a Management Studio. I still owe a blog post on how to make that work. So with R2 you know Management Studio is really rock solid.
Greg Low: Actually with custom reporting in Management Studio is there, I suppose any move to get, I suppose a later version of report viewer so that it can run the reports built on the same version but I suppose that’s more an issue for the reporting services team but it always seems a pity for example in 2008 you had to use 2005 to build the reports to run in Management Studio. In 2008R2 you have to use 2008 and so on.
Bill Ramos: No, no, no, no, no. In 2008R2 you have to use 2005 version still.
Greg Low: Still? You can have the later version report viewer.
Bill Ramos: No, for R2 they didn’t release the report viewer. It wasn’t until.
Greg Low: That was in Visual Studio 2010 but of course they didn’t take a dependency on that did they?
Bill Ramos: Yes, yes, now for the Denali release in CTP1 we were shipping.
Greg Low: We still say we for Microsoft?
Bill Ramos: Yes, I go to remember that I have this Microsoft hat as well as part of the training and evangelism role that I have here. So if you hear me say we, it is often Microsoft. I am still getting use to my three weeks from Microsoft, since I left Microsoft. So with Denali on CTP1 we took the old Visual Studio out and put in Visual Studio 2010 but we still had to use the old report designer. And that’s because it took a little bit of work to actually to get the designer hooked in the way that it needs to. So I think by the time the next public CTP comes out, the team will have the new report designer so that you can finally use Report Builder 3.0 and all the tools in BIDS. The current tools to actually design reports that will work inside Management Studio.
Greg Low: Yes, it just seemed like a nice capability to be able to do the custom reports but then almost as soon as it appeared in what was Service Pack 2 of 2005 it kind of felt like it was nobbled from that point on so yes.
Bill Ramos: Yes, but I tell you Greg, ever since I started taking the reports that we had in the Management Data Warehouse and recreated them for SQL 2008R2 Reporting Services using the Report Builder 3.0 on top of that, boy I just love running the Manageability reports under Reporting Services because you can cache them up, you can send them out to people, or more importantly you can copy and paste text from the reports.
Greg Low: Yes.
Bill Ramos: Plus all the great export features that you get with the web based reporting system.
Greg Low: Actually the final thing I will ask about Management Studio before we move on is probably the old bane is just the extensibility story. You would know it’s usually top of the list of everything. Just out of all the teams in Microsoft it sort of feels like the SQL team is the most conservative or the least willing even in the tools area to sort of open up extensibility and it always seems such a pity to me. I mean I look at areas of the product say for example Service Broker where I thought in 2005 they did outstanding plumbing but didn’t give prescriptive guidance and didn’t give any tools. Then picked other people would have been happy to step up to the mark to provide those tools. Yet in 2011 coming up now we still don’t have those sort of tools even though people would of built them in 2006 if there was a supported add on mechanism.
Bill Ramos: Well. I said a lot of people have hacked into the extensibility interface with Visual Studio to actually create some remarkable add ins. There is a company down in Oregan in the US that has Service Broker addin that helps you build a Service Broker app. The guys are Red Gate has the SQL Prompt tool have done a great job at hacking and I saw.
Greg Low: Melan Predic with his SSMS add in toolkit is wonderful.
Bill Ramos: Yes looking forward, I think he has just got a few more days of testing before he is ready to roll that out for Denali CTP1. The cool thing that I saw when I was at SQL Century back in October, I saw the integration with Management Studio where they were actually launching dialogues from Management Studio in their UI. Dudes how did you do that? You know, it was so fun back in the old days when 6.5 Enterprise Manager we had SQL Name Space. Which was basically where customers could go ahead and plug in and use any of the dialogues that were used by Enterprise Manager in the apps. In fact Access used that interface to bring up the backup and attach style logs for doing stuff with Access database projects, as people were extending it. However Greg, a teaser in this for all your folks, you should did out the Connect item that says please add add-ons. I will tell you that was the number 1 voted items of all the Connect items that were active right now. Exactly, it is still active and when I last talked with the Program Manager who is responsible for the feature now, he said he was thinking about it. We have got to get him to more than thinking about it, so you know we have to use the community and beat up on him because I think there is a tipping point now. With 2010 Visual Studio they have the Microsoft Extensibility Framework which makes it pretty easy to.
Greg Low: To do this, yes, exactly.
Bill Ramos: To do this right? I know our Dev on the team when it was doing the extensibility site because we did a lot of the work on the Redmond side was saying ‘hey Bill, can we sneak this in?’ I said ‘not so sure that is a good idea, quite yet, because part of the problems is always around testing, right. When you test out something like that, you know, you got an obligation to your community. But I think there is growing momentum on the team to think to see what they can do by the next public release to get it out.
Greg Low: The way I look at it is often when I go along to say a Software Design Review the discussion on version 1 of anything. I am usually the person in the room putting my hand up and saying what about extensibility and they always tend to say not in version 1 but in a later version we will look at it. It always strikes me that version 1 is the time when you probably have the most holes in what you do and other people would be happy to step up to the plate to try and help make it. Otherwise you are left in a loop where you have to have thought of everything in version 1 and that’s not often the story, right?
Bill Ramos: Yes, I think Microsoft in general was thinking about their version 1 and the extensibility architecture and making sure that they do that. Maybe the climate of the team at the time that prevented it from happening and you know. With 2005 we were really rushing to get that out the door and so it was kind of hard to think about extensibility. Now I will tell you this though Greg, the team actually looks at extensibility first and they actually design the tools now around extensibility framework for plugging in. So they are ready to go.
Greg Low: That’s a better story, that’s good.
Bill Ramos: Yes, so it’s only a matter of time I think before the team flips the switch and gets things going.
Greg Low: Cool.
Bill Ramos: I think it is time for the MVPs to march on Redmond and go. Or wherever and say ‘we want extensibility.’
Greg Low: I am sure you know the story there.
Bill Ramos: Yes.
Greg Low: So the thing I wanted to talk about, here was also Data Collection and management of servers. I know it is dear to your heart and probably, I think in everybody’s future there seems to be System Center Virtual Machine Manager or MOM or some of these products. I find most of the SQL DBAs have not looked at or touched any of those at all so it’s probably good to get their head around where you see this all going?
Bill Ramos: Well thanks Greg. So I think System Center is specially Operations Manager is one of those tools that DBAs haven’t quite yet figured out unless you are a DBA at say Hewlett Packard.
Greg Low: Yes.
Bill Ramos: Hewlett Packard has figured this out and what they have done is that they have created a custom made management pack built on top of the SQL Management pack where that is their primary interface for the DBA to see what is going wrong with their systems. The cool thing about it is just the ability within System Center to look at your application at a high level. So for example if you have got an application that is order processing at around Christmas time and you are Amazon and you expect 10000 orders per minute for example. You can actually write a data collection around your application within Operation Manager to track that and see the trends so that if all of a sudden you see your orders all of a sudden going down and your CPU starting to spike. It could be the fact that maybe you have a query that’s gone bad on you. All of a sudden the query plan changed all the customers is taking the system longer to process the order or all of sudden the web app just stopped performing and customers give up and move to someone else to do their shopping. So the online world that could be pretty ugly.
Greg Low: Yes, in fact I have got a customer who probably most of the time has 20000 concurrent users and one of the things that is interesting is that they had a big plasma screen in the room where all the developers and operations staff were and it was being used to display activity and they were grabbing it out of MOM or SCOM. What was interesting was that when I was doing some tracing in the performance tuning the queries that were coming through that were actually the heaviest queries on the whole system and so the way they had done it. It was interesting they had people in their normal application going to extraordinary lengths to build good queries but then the guys that were doing the monitoring in fact wrote the worst queries and they ended up hurting the system more than help it. But it was all just so they could display a thing on the screen on the wall so that they would know how busy it was.
Bill Ramos: Yes, it kind of reminds me of when we first put together Activity Monitor for the 2008 release. We had fired that thing up and had it refreshing the screen like every 5 seconds and then we realized that Activity Monitor itself was consuming like 10% of your CPU.
Greg Low: Yes.
Bill Ramos: And the query to get the most expensive queries was the most expensive query.
Greg Low: Exactly. Always a lesson there about not killing yourself with monitoring yourself.
Bill Ramos: You know I talked with System Center team quite a bit over the last 3 months, just understanding management packs scenarios and how to do Data Collection. One of the wise sayings I got from Principal Program Manager there Dan Rogers was that less is more. The less you actually capture, the more value you can get out of the product and so you think about how often do you need to see if a disk subsystem is running properly when the amount of time it is going to take to fix the problem is going to be really long.
Greg Low: Yes.
Bill Ramos: Right and so we were thinking about this even in the high availability space and database mirroring. If your secondary goes down, do you need to know within a second or within 10 seconds or 15 seconds that it went down? When you do find out, it will probably take you a day to order a new computer and then get mirroring overnight.
Greg Low: That’s interesting, I saw that survey that came that we all filled in. It was interesting the questions in that where looking at exactly those sorts of things. It was, if you have this happen, how long is it before you really need to know about it.
Bill Ramos: Right.
Greg Low: In terms of response times and so on and how long it would take it for that to realistically be repaired and so on.
Bill Ramos: So it all comes down to what your business is willing to tolerate and how fast it can react to hardware problems. I mean if you have a hard drive go out, do you have a bank of hard drives that you can just slap in? I don’t know, I don’t have that many here.
Greg Low: It depends on the company.
Bill Ramos: Yes, it depends on the company. Right.
Greg Low: So, in terms things that are already in the product, we have the Data Collection System that came out 2008 what’s your feeling on the current state of play for that?
Bill Ramos: Well it’s interesting Greg, that at SQL PASS this year I found more people interested in the Data Collection and the Management Data Warehouse capabilities than ever before.
Greg Low: Yes.
Bill Ramos: I know the reason, you know. We are finally getting off 2005. I remember at 2007 at PASS doing a presentation on top of CTP6 at the time or CTP6 was about ready to roll out. And you know the first question that came out of everybody’s mouth was ‘will this work against 2005?’ ‘No, sorry.’
Greg Low: Even that is an interesting trend because if you look at most features of the product it makes complete sense to, if you are trying to push a new version of the product to have it only work only with the new version but I would suspect that anything that is involved in manageability if it doesn’t work backwards a version or two you almost preclude it from being of any interest to someone when they are looking at upgrading.
Bill Ramos: Yes, I think we found that out, loud and clear. Especially with this feature is that it has taken a while to catch on, but now people are actually starting to bring it up. We had some folks at Dell computers, say ‘hey we want to give it a go with 50 computers. What kind of hard ware do I need to handle 50 computers collecting into a single MDW?’
Greg Low: Have you seen anybody build a collector for 2005? I know it was on my to do list at one stage but I didn’t get around to it.
Bill Ramos: There were some folks in EPG or somewhere trying to hack out the packages in SSIS to make it work on 2005 but the big problem there was that in 2008 the SSIS team made several key bug fixes that allowed the Data Collector to be performed at and responsive in this continual data collection that we have for the Data Collector. So if you are trying to use 2005, I am pretty sure it would AV in a heartbeat.
Greg Low: Yes, I must admit, that I was thinking at the time that I would have had to build my own agent instead of using Integration Services for that. Anyway regardless, so that’s not commonly done anyway.
Bill Ramos: That’s where we have some great partners like Red Gate, Quest, SQL Century where they have dedicated products. I am sure I am forgetting. Idera, oh yes. They handle all the versions of SQL Server and they will fill that gap and continue to fill that gap but right now it is amazing that the Data Collector in the MDW are starting to take off and I think with the blog posts that published the rdl files that can now run in Reporting Services. That was the other question that everybody asks was ‘ how can I take those reports out and run them on Reporting Services?’
Greg Low: Yes.
Bill Ramos: At the server level, and you know at the time the rdl files that we had in the product, you just couldn’t take them as is and convert them because inside we think about how we can handle multiple languages because all the strings inside those reports are all references to dll resource right? It is almost impossible to figure out what is going on. Plus many of the queries stings use the parameters and those who are all C style parameters with %1 or %2, %3 that kind of thing.
Greg Low: Yes.
Bill Ramos: So it is a difficult thing.
Greg Low: Yes it would be fairly horrible to do.
Bill Ramos: So it is just as easy to take the source files and then literally recreate them from scratch using the data source. You know all the great goodness that was put together in terms of stored procedures that help run the reports on the MDW. The exercise to covert those reports, now people have Report Services they can run against their Management Data Warehouse and with these reports we actually recognize the MDW reader roles so that. We don’t have any references to MSDB in them like we have inside Management Studio.
Greg Low: Now that’s good actually. In the product over the years I think they have done an amazingly good job with the localization of strings with things like. Probably the one exception for me is Policy Management curiously. In that one, you have to create a different policy for each culture and that strikes me as completely bizarre. You know how that happened?
Bill Ramos: No, I won’t even pretend to know how that happened.
Greg Low: Because I remember I went along to the SDRs and I am looking and everywhere they had, they just had like a spot you typed an actual string into and I kept saying ‘how do you localize that?’ In the end what they did was interesting is that they path now includes in the culture part of the path. So if you have to have a policy but if you got to do things in four languages you have got to build four policies. It didn’t seem the cleverest bit of coding I have ever seen.
Bill Ramos: Yes, so SCOM is System Center Operations Manager isn’t that much better but the management packs can be localized and indicate which one is which. Each language has basically all the strings embedded in it. So you take the set and you include that management pack depending on the language you want to run against and you can see things localized. The story is not that much better, but I think long term I think there is going to be more effort on Microsoft’s part to make System Center really deal with these kinds of situations. So their authoring environments can deal with it.
Greg Low: Back on Data Collections, as I said I have found the same. We have been out talking to people about it and more and more people are starting to get involved in putting it in. One of the critical comments that come up all the time is, people ask that there doesn’t seem to be a way to remove it without hacking it out. Is that likely to appear somewhere along the way? One of the hesitations people seem to have about putting it in is that there is no option with taking it out.
Bill Ramos: Why do you want to take it out? What’s the problem?
Greg Low: Yes why would you ever want to?
Bill Ramos: I mean you can. Once it has been disabled, at the Data Collector it is pretty much not doing anything. The only thing you have to watch out for is the purge job which will fire. So the thing do is just disable it, that disables all the associated jobs that do the data collection so nothing is running in SSIS. There is no DC Exec process as far as SSIS goes, so it is pretty much turned off at that point. So just turn it off and be done with it.
Greg Low: Okay.
Bill Ramos: So ignore it, right? That’s what I say and you can script it out in Powershell. You can script out in TSQL through a stored procedure interface to turn it off. That’s the way to do it and if you are concerned about the jobs that are there.
Greg Low: You know you can always nuke the jobs.
Bill Ramos: Nuke the paths. The nice thing, is that if you go ahead and run the wizard again or actually run repair in setup, it will rebuild a lot of the stuff and you can also rebuild it through the wizard interface when you go to setup data collection. That is pretty much put it back into place.
Greg Low: It just seemed odd, to have a wizard to set it all up but no to have an option to have it all go away again. Anyway.
Bill Ramos: We never thought about it during the product design cycle, we said ‘if you don’t want it just disable it. It’s just done’.
Greg Low: Fair enough.
Bill Ramos: It’s exactly the reason why there is no UI for creating custom collection sets. If you are going to create a custom collection set you better really think about what you are collecting.
Greg Low: I should actually play almost acronym police or buzzword police. Many people might not be aware of custom collection set, so maybe a quick definition of those.
Bill Ramos: Within the Data Collector, it is an extensible system that allows you to create a custom data collection set, that includes collection items. One or more collection items and they can of type SQL trace which would actually run a server side trace on the target machine and take the information from the server side trace and dump it in the MDW. You have the TSQL collection type that can be used as part of a collection item that will run any kind of TSQL query. There is an interface to run it against, all the databases or selected databases on your target machine. There is also Perf counters, where I think that is the biggest value and by the far the easiest thing to report against. So you can take any perf counter that Windows has on that system and collect data about it.
Greg Low: Does that use DMV, sys.dmos performance counter DMV to get to those?
Bill Ramos: No not at all. So the DMV you are talking about only looks at SQL Server perf counters.
Greg Low: So this allows you to get to any of them, does it?
Bill Ramos: Yes.
Greg Low: Nice.
Bill Ramos: Network IO, disk reads, disk writes, things that are specific to the OS. Percentage of CPU utilized for the instance. With that data you can create a custom collection set, you have to keep in mind that we have a Server Activity Collections Set that already collects a lot of the most popular perf counters based all of the white papers and CSS. I know Andrew Kelly or someone like that will go, you are missing page life expectancy. I think page life expectancy is in there.
Greg Low: I was going to say, I think that would be there actually.
Bill Ramos: Yes, it’s actually there. When you set up one of these collection items, you define a collection set that it is associated with. The ideas is you can build up a little troubleshooting package with the collection set that looks at a specific scenario that you might have in mind. It can include how many collection items. I don’t think we have any limit on the number of collection items you can associate there. The idea is, that the collection set is that the atomic package. Each collection item has its own frequency which it can collect at. It can go down to 5 seconds which you better be careful when you do that. We have a couple that go 10 seconds but most of them are 60 seconds for the server activity so that we can actually, The ones that go at 10 seconds have to deal with the locks and blocks, so that we can see the blocking chain and we have got a sample a little faster than normal. We didn’t have X events back then. That would have been easier.
Greg Low: One of the things I find even with blocking chain though, you might be wanting to track down a specific one that is an issue at a point in time. I find that most of the time by the time I have got rid of most blocking systems. Well, first up the thing I find about getting rid of blocking is if you make the queries run quick most of the blocking disappears.
Bill Ramos: It’s a short transaction.
Greg Low: A number of sites we get called into to deal with blocking issues and all it is that you make the queries run fine and all the problems disappear. I find that once I start getting into that, what I do eventually start looking for is I use to build a little job that fired up even every few minutes or 5 minutes. It would just look to see anything that was at the head of a blocking chain and I would squirrel that in a database and go back to sleep again. What I found interested, was over a period of time, it was that query. Or something was the thing that was causing or tended to be at the head of a blocking chain. So I might have another look at that one.
I didn’t find that even when I was doing that sort of thing the instantaneous granularity was what I was after. Even in my case it might pop up every 5 minutes, if I did that over a long period of time I would get a good trend.
Bill Ramos: See that’s where one of the things around the Data Collector and MDW that I have been trying people to do is actually get the development team to fire it up. That way they could do their little test workloads and the MDW can report things like, blocking. More importantly because it has the tie in to the ability to report server side trace you can take that server side trace and feed it into DTA and figure out your missing indexes.
In addition you can use your capabilities that we have with Query Statistics and find out if you are having query plan issues. I have to mention Bart Dunken and the latest post he did where he created a new collection set that can replace the query statistics collection set that we shipped with 2008. I have to dig up the URL for you so you can put it in the podcast. The Query Hash Statistics is on TechNet somewhere and what it does, it uses in 2008 at the very end of the release we added this thing called SQL Statement fingerprint and query plan fingerprint to the DMVs that keep that information so that we can check for fast running queries that maybe change plans a lot. You don’t realize what is going on and because it is so short in duration it doesn’t show up as a long running query but when you aggregate them together you go that is a nasty query.
Greg Low: Yes, it is one of the things I look for in tuning all the time is things. It’s interesting a lot of things always filter out the expensive things but like again I was looking at a system a little while ago and one of the things I found is a query say that was running 10000 times a minute with exactly the same parameters.
Bill Ramos: Yes.
Greg Low: You go, okay now it’s taking no resources at all in theory for each query but
Bill Ramos: When you add it up it is taking 95% of your CPU.
Greg Low: What’s going on here? What was interesting in that case it was simply a typo in the caching code. Every time they done the right thing, it cached this stuff but every time they looked in the cache they would say it was not there and then put it back in again. It is very interesting that with that sort of thing it is very hard to find any other way except at the backend.
Bill Ramos: Yes so with this new Query Hash Statistics Collection Set that Bart Dunken has put together, it is dramatically decreased the amount of disk space per data that is collected for query activity.
Greg Low: That is the final thing I want to ask you about Data Collection is the impact on the systems and on the server that is holding the Data Warehouse?
Bill Ramos: On the system that is being monitored, we did some performance testing back when we released the product using TCP Benchmark. What we did was measured the impact of throughput is the actual number you have to worry about and we found that it was just a little under 5 percent, less throughput with the Data Collector on than not having it turned on. That was our goal, we wanted to make sure that we were right there under 5 percent. We figured that the value it has to have the Data Collector on all the time, we wanted to make sure it doesn’t impact the system.
Greg Low: I configured it and I must admit, that on the sites I have put it on and it has just been wonderful. I don’t see an issue at all. I have heard the odd rumble from people on the MVP forums and I don’t have any evidence of that. They were talking about some instability in some of the SSIS jobs but it just has not been my experience. I have had a good outcome with it.
Bill Ramos: Yes, in general it does run pretty good. The only time I see that it can get a little frustrating is when I have in my demo environments when I am using a 12GB box and I have all these VMs set up and have SQL Server with a 1GB VM and use Brent Ozar’s recommendation and give half the memory to SQL Server. What will happen is you will see it and when I am demoing it I am actually having data collect change the default upload time from 15 minutes to 5 minutes. So what will happen is you will see a steady load on the system and then Data Collection kicks in, CPU drops down. You get little waits as the data is getting pushed into the MDW, on the same box, on the same VM, on the different VM. The system itself, if you have separate computers and things like that I am sure you won’t have the level drop that takes place every time you do a data upload.
Greg Low: I have been pretty happy with it in that regard.
Bill Ramos: Yes, on the MDW server itself, you just need a lot of disk space. The data uploads every 15 minutes, we did a lot of work with the engine team to solve some interesting dead lock issues that were nagging us in earlier CTPs during the 2008 release cycle that got fixed as part of our troubleshooting. The Data Collector before we actually released 2008. We tested up to 120 systems all uploading at the same time and every 15 minutes without any problem.
Greg Low: What sort of guidelines do you currently suggest in terms of disk space?
Bill Ramos: Well the disk space is relatively easy to compute because we have a Disk Usage report that shows you how much disk space the MDWs are growing per day. You can take that value based on what you are collecting and see the amount per day and go okay how much do I want to retain?
Greg Low: I think that’s a good point is that what you determine from that is the retention period.
Bill Ramos: Yes, so the default 14 days is the retention period but if you are running on a tight system you might want to decrease that. In terms of what’s actually collected with the out of the box Data Collectors all turned on for the Management Data Warehouse and the Data Collector you are looking at anywhere from 250 to 500 MB per day per instance. A lot depends upon how much is actually running on the target machines. For example one of the things that we do, is we go ahead as part of our perf counters, we look at all the percentage of CPU utilization for every process running on the machine. So if you have a SQL Server box running on a Windows Server core box, you don’t have a lot of stuff running. So we are not collecting a lot of process information. Likewise if all your queries are all basically the same, especially with Bart’s new query statistics hash stuff, data collection drops down a lot. You still looking at, again, your mileage may vary but that’s what we say happens.
You don’t need a very beefy machine in terms of memory and CPU. For the actual MDW server and if fact in my typical scenarios is what I will do is. Is right now I will run the MDW database actually on my System Center Operations Manager box so that I have both the MDW and SCOM data collections all happening on the same machine. It works pretty good for me.
Greg Low: Good. So look, another thing Bill that introduced in 2008R2 was SQL Server Utility and that one has sort of struck me as kind of odd fit in the mixture. I am just wondering your thoughts on that?
Bill Ramos: My thoughts on the utility, the utility for what it is intended for. The idea is to create an out of the box experience that would allow you to look at a bunch of servers that you have under your control and understand the CPU pressure and disk pressure and so they were designed around those two areas to start. So for example is my system capable of handling the load for the number of instances that I have on that box? Am I in a position where I might run out of storage for a particular instance and then be able to report on that? Out of the box, we didn’t have anything that would easily identify that of information.
With the utility the idea was to come up again. They used the Data Collector for the core data collection and they actually created a bunch of staging tables which you see when you do install the utility.
Greg Low: Yes I have seen that in the MDW, the tables that it creates. It seems to always give me the feeling that it doesn’t feel like it’s compatible with the data collection stuff sort of thing.
Bill Ramos: Well, that’s because it forces you if you were running Data Collection already if you want to add that instance to the utility. You literally have to stop Data Collection on that instance and then
re-direct it towards the utility after you enrolled the instance into the utility. Once it is there Data Collection works just fine against the utility, so the idea is to have the one place for all your manageability data. For that it does a pretty decent job, the interesting thing is that you know the limits you hear about on the recommendations for how many instances that it can support. It was interesting that when we developed those guidelines it was because we had kind of turnkey machine running our utility and we didn’t have a real hard drive system. Real raid drive configuration and we were concerned that customers using the utility control point would end up with these problems in throughput into the utility.
As we started with Denali release we got to looking at the hardware configuration a little better and realized that if you have a properly configured server with real raid disks. Utility can actually handle quite a few instances reporting into it. So the, people look at it and go that many instances? I have a heck lot more instances than that.
Greg Low: Actually the biggest concern I have heard in that area, is around the licensing. In fact the Enterprise Edition was limited to 25 instances and basically the word I keep hearing is well. How run Enterprise Edition and has less than 25 instances?
Bill Ramos: I think that is going to change pretty quickly especially with the Data Center skew not getting a lot of attention right now. We will see how that works out with the Denali release in being corrected. I can imagine that actually being. Let’s see if it gets corrected in the SQL Server 2008R2 release, I will check with the marketing teams.
Greg Low: It did seem like a very strange thing where it almost. It seemed like part of the product that again could actually be killed by the licensing if nothing else.
Bill Ramos: For what it does, it does pretty good but that’s where I think tools like. You have Policy Based Management that they created on top of thing that allows you to write customize a policy when the CPUs exceeded so much percentage over a certain amount of collections that you can control the house state of that. All of that is behavior that can be found right now in System Center Operations Manager which doesn’t have the licensing restrictions as Utility Control Point has.
Greg Low: Exactly. In general in many of the people that listen to this would not be familiar with the System Center suite at all. Perhaps maybe quickly mention what is in the suite and what parts of it is of interest to the data folk.
Bill Ramos: There are all sorts of things. You mentioned System Center Virtual Machine Manager, there is Operations Manager which is the one I am most familiar with. There is System Center Configuration Manager, one of the things that people often say is ‘how would I know that DBA is responsible for this instance?’ or ‘how do I know the DBA is responsible for this database?’ There is no way in SQL Server that you can actually record that unless you are using an Excel workbook or something like that.
Greg Low: Or Extended properties on things.
Bill Ramos: But even then you don’t have Extended properties on the server. System Center Configuration Manager actually records that information so you can keep track of it. That’s a tool that is when you put it in the hands of the DBA or the application it can make a difference shortening your time, response time and finding out who to call and things like that when the system goes awry. I am sure I am missing bunch but again my sweet spot in learning about System Center is all around Operations Manager. What I learned about that is that it has some very sophisticated infrastructure to allow you to define things like policies over periods of time with performance base lining built into the product. Select a particular computer or instance and have context sensitive tasks to help you to diagnose the problem on an unhealthy system. Even has the capability for certain types of monitors and rules to fire off a remediation action that says ‘maybe do some more detailed tracing’. For example you might want to fire off a X Event trace to do a deep dive tracing on a particular query running on your system or you can have it fire up a new Data Collection Set that you have configured as part of a deeper discovery action, then take remediation. For example let’s say we implemented a new thing with System Center, what the MOM pack for 2008R2 that we released in August. Is this blocking session rule that we built in here? In that we could have built a remediation that said if more than 10 sessions were blocking over a 5 minute period of time, kill the head block. You can actually set that role up into operations manager, of course you also say prompt the user to say do you want to kill the spid.
Greg Low: Or go and talk to the human.
Bill Ramos: Make sure that it’s not the VP’s query that finance you. So with System Center you have a lot of flexibility. What I like is its’ ability to create these little dashboards that allow you to see charts of your data over time like you would with the Management Data Warehouse or you could see health state information. You can build your own dashboard environment on top of the existing management pack. It gives you tremendous flexibility.
Greg Low: If you look two years down the track, I mean, where do you think the DBAs will spend their time in monitoring. Will it be more in MOM type area or SCOM?
Bill Ramos: I actually think it will be in SCOM. What we need from somebody is a redoing of the existing SQL MOM pack that has the DBA in mind. The current management pack that we have for System Center was basically a version that was copied from 2000 to 2005 to 2008 to 2008R2. Without regards to the way the DBA may want to see the information or respond to the information. So one of the things I was doing right before I left was coming up with the new design for the management pack to incorporate what are the key performance counters that DBA care about based on the work we did with the Data Collector management in Data Warehouse. Start collecting those values as part of monitors and roles within System Center and then from there you can create thresholds for those values. For example if you expect your system to be running over a particular instance say 60 percent CPU and all of sudden it jumps to 80 percent you can actually change the health state and fire off an email to your DBA saying ‘look this thing went into an unhealthy state, do something about it.’ They could then fire up System Center look at the instance that is unhealthy and launch Management Studio. Even with System Center you could take those same rdl files that are recreated running under Reporting Services and actually run those assuming you had some form of Data Collector running on that instance as well. Even within the SCOM environment you could run the same rdl files.
Greg Low: Do you imagine that the collection will continue to be within SQL Server into a warehouse then SCOM talks to that? Or do you imagine somehow eventually the collection would be straight to the box that is doing the SCOM work instead?
Bill Ramos: I think the trend will most likely be SCOM to do the data collection for the light weight things it does best. SCOM is really good at collecting perf counters and Windows event data. It is not so good at SQL queries.
Greg Low: Yes.
Bill Ramos: What I am suggesting take the Data Collector stuff where we were collecting the perf counters and let’s flip that over and have System Center collect that information in its database and then take things like the query statistics work and continue to use the MDW to do that. I found that if I took Bart Duncan’s Query Statistics Hash Data Collection set and just ran that. Disable server activity and disk usage activity or the disk usage report and have my counters working in SCOM I can effectively find the root cause to any problem within System Center and launching over to Management Studio and the MDW reports. Just as easily as I could within Management Studio with much, much less overhead in terms of collection because again less is more when it comes to System as I don’t have to collect as often as I did. So the blocking chain scenarios that you mentioned earlier, you don’t have to do that all the time.
Greg Low: To me the ones that are most interesting were those that have a longer term trend. There will be the odd one, but again Activity Monitor we can already drill in and look at what is going on right now in terms of that.
Bill Ramos: Exactly.
Greg Low: I tend to be more interested in things that tend to cause that over a period a time rather than necessarily things that are problematic right at this instant.
Bill Ramos: That is what System Center is designed to do. You can actually do base lining for a particular set of counters and then see the difference from month to month, week to week, year to year even. If you wanted to. That is something they have spent a lot of time on designing into the product. It is only going to get better from the sneak peaks that I have seen with System Center 2012.
Greg Low: I think it is an interesting suite of tools, yes I mentioned Virtual Machine Manager again because that’s one we have used quite a bit. In fact we did an eClinic for the SQL team on SQL Virtualization. Bill Chesnut one of our guys did that a little while ago and we worked with him on that. It is interesting that again rather than trying to manage multiple instances of a machine it is becoming quite common to virtualize all of those and use Virtual Machine Manager gives you really outstanding flexibility in terms of managing the virtual instances across machines that are trying to manage SQL instances on the one box and so on.
Bill Ramos: Yes.
Greg Low: The tooling is really quite good. I suppose from here then Bill, so where are we going to see you. Where will people see you or what’s coming up?
Bill Ramos: What’s coming up, part of my role is training and evangelism at Adviya. So my next public appearance is actually going to be at Tampa Bay for SQL Saturday 62 in mid-January. In February 19th, I think is SQL Saturday 42 which was cancelled that’s why it’s not happening. I will be in Phoenix doing SQL Saturday session around Data Collector, MDW storage and then they invited me to talk to in Vancouver Canada, the next week. My wife and I will drive up from Redmond area and give a session there.
Greg Low: Catch the train, it’s outstanding.
Bill Ramos: I know you can catch the train, it’s always nice to have the car.
Greg Low: I should mention to people, people will realize I spend so much time on planes that I am kind of over airlines. One of my favorite trains in the world is the one that runs from Seattle to Vancouver. It is about $30 and in most other countries it would be a scenic train trip. It runs right alongside the water and then through the mountains, the snow. It’s outrageous and it’s huge and comfortable and room for a laptop and power and everything.
Bill Ramos: The only down side, we want to take our little dog with us.
Greg Low: Oh yes, okay.
Bill Ramos: We have a little Japanese 10 pound guy and it goes everywhere with us.
Greg Low: Yes in general I still end up on planes in enormous amounts and I do try and avoid them where I can. That train is one in particular that is pretty good. Actually it is quite scary watching that Qantas jet the other week that. What did they call it? An uncontained failure in the engine, what is particularly scary with that is Mai and I have been on that one twice in recent weeks prior to that. That’s when it starts getting a little more, very realistic. When you realize that very same plane you have been on. Anyway.
Bill Ramos: So I am actually in my new role and going to be out in the community a lot more than ever before than I was at Microsoft. I am going to continue to blog and tweet. I have got my blog account set up on SQL blog.
Greg Low: Great, Adam and Peter’s site. Excellent.
Bill Ramos: It turns out I have still have access to my MSDN accounts. I can always use that, I have got my twitter at BillRamos still active. Whenever I see something interest I will fire Twitter.
Greg Low: We will be following it.
Bill Ramos: I am making a run at MVP for the next cycle.
Greg Low: Excellent.
Bill Ramos: We will see how that works.
Greg Low: Great! Well thank you for your time today Bill.
Bill Ramos: You are welcome Greg it was a pleasure doing this podcast with you and can’t wait to hear this.
Greg Low: Exactly. Great.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement