Curious..about data

TSQL Puzzle

Today’s TSQL post is an answer to a puzzle posted by Adam Machanic. I learnt something new via this puzzle today. The puzzle is as below – what would this query return?

SELECT * FROM(VALUES(1),(2)) AS x(i) WHERE EXISTS (SELECT MAX(i) FROM (VALUES(1)) AS y(i) WHERE y.i=x.i)

Upon looking at it, I thought I would get 1 row (1) – ecause the second where condition was looking for matches to 1. But I got two rows, 1 and 2. So I broke up the query as below to dig further:

SELECT * FROM

(VALUES(1),(2)) AS x(i) WHERE

EXISTS (SELECT MAX(i) FROM (VALUES(1)) AS y(i) WHERE y.i=x.i)

–this returns two rows , 1 and 2

SELECT * FROM (VALUES(1),(2)) AS x(i)
–this also returns 1 and 2

SELECT MAX(i) FROM (VALUES(1)) AS y(i)
–this returns 1

SELECT MAX(i) FROM (VALUES(2)) AS y(i)
–this returns 2

SELECT * FROM (VALUES(1),(2)) AS x(i) WHERE

EXISTS (SELECT MAX(i) FROM (VALUES(1)) AS y(i) WHERE 1=2)

–Bingo, I got my answer!! Even if exists clause is null it still returns two rows , 1 and 2.
This was a very interesting discovery and one that I will always remember when using MAX in and embedded select. Thanks, Adam.

TSQL-Paging through a resultset

I have been out of touch with latest features in TSQL for a while now. That mostly happened because my dba gigs in the past few years have not involved a lot of programming. To come upto speed with TSQL, am working with a book TSQL recipes written by my good friends Wayne Sheffield(blog|twitter), Jason Brimhall(blog|twitter) and some others. Am going to pick one ‘recipe’ from the book for each blog post and write of what I learnt from it. Today’s recipe is a simple problem –

‘You wish to present a result set to an application user n rows at a time, how will you do it’.

This lead me to research two keywords in TSQL – OFFSET and FETCH NEXT.These are used together. What Offset does is to tell the
resultsets to skip first <x> rows, and Fetch Next <y> rows grabs the y rows after the offset. So suppose my requirement at a more refined level is to return 100 products at a time to the application, ordered by product id – I would go for a stored procedure as below:

CREATE PROCEDURE Paging100Rows

@cycle INTEGER

DECLARE @offsetrows INTEGER, @fetchrows INTEGER

SET @offsetrows = @cycle

SET @fetchrows = 100

SELECT ProductID,[Name] FROM [Production].[Product]

ORDER BY [ProductID]

OFFSET @offsetrows ROWS FETCH NEXT @fetchrows ROWS ONLY;

So if I wanted the first 100 rows, I would call it as below..for the next 100 rows I’d pass 100 and the next 100 rows 200 and so on.

paging100rows

What to look out for:

1 The sort order must be very specific and related to the rows you want returned.
2 Any change in isolation level will impact results with dirty reads/non repeatable reads etc. (as it does with any select statement of any kind)

Keep the Quorum…

As a follow up to the earlier post I had on harassment issues at PASS Summit – writing this to clarify more on the same. The community seems divided on if or not issues should be reported, if or not those who do not report are guilty, if or not those who say ‘I will report’ are wrong, on and on. There are no easy answers to any of this, and a healthy debate is a good thing, for sure. But what happens in most cases is that people with strong stances either way tend to back off, thinking they are ‘wrong’ or ‘don’t belong’ and it leads to a sort of a stalemate, allowing such things to go on as is. Below are some of my experiences and humble opinions on all of this.
1 Should all issues be reported?
The answer is umm..no. There are lot of issues with making a blanket statement like that.Any of us who has been a victim – not necessarily at PASS but anywhere knows the issues around making a shaming incident public.You are already suffering shame, and need privacy. And then you have people expressing how they feel about it, you have people taking stances that it did not exactly happen like you said, you have gossip, cliques, on and on. It is not a pleasant experience and the victim has a right to opt out of it if he/she chooses to. In this case it can also be that victim really does not trust the body of people the issue is being reported to. In a close community that kind of opinion is not uncommon.

2 Is it then necessary even to have a structure to report issues?
Yes, it absolutely is. And again, that is the problem with polarized stances. If you don’t report an issue, chances are pretty high that person is going to try it again – on someone else, at someplace else. All criminals get to be that way because they got away with something,somewhere. And again, you may be from a country or a culture where such incidents are really rare. Where if someone engages in butt pinching the woman in question will just turn around and put him in place before he could bat an eyelid. I have known women like that from Germany and Sweden, and even from my own home country, India. There are women who will turn around and make a sarcastic joke, or pinch him back. The problem is that not all people are that way, nor do all of us have presence of mind to act in that way. Some of us feel incredibly violated, shamed. And not all incidents have to be as mild as butt pinching. It can be a lot worse. A lot of us come from cultures where people will stand by and watch abuse happen, even enjoy it and egg the perpetrator on. A lot of us have been saved by many a unknown hero who saw it was wrong.
A structure to report the incident to, to ban the person from conferences, report him to police or similar can and absolutely should exist. Men or women who want to be guardians or make themselves available as guardians are AWESOME PEOPLE, they should not be shamed or let down for what they offer to do.

3 What if I see something that looks like harassment and want to report it?

Check with the victim as much as possible. And if that is not possible I hate to say it but I would probably report it. But asking the victim helps – and understand what he/she would prefer to do.

4 What if someone confides in me and asks me not to report it?

I would not report it. Respecting someone’s need for privacy is important. I would definitely ask if he/she did anything to let the perpetrator know he was behaving badly. And if it is ok to report it anonymously. I would not in any condition disclose the victim’s name to anyone else or shame him/her for not reporting.

Let us keep the awareness that taking unfair advantage of anyone is wrong, no matter how your own culture chooses to call it. The fine print on that stops if I say I have been taken unfair advantage of. It is wrong to say things like ‘in my culture that would have been a non issue’, or ‘you are too sensitive’, or that ‘You should have reported it’, or ‘You should not have reported it’. All of those thing are opinions , and opinions are necessary to make our process better. But we do have to accept that abuse happens, and work towards bettering trust and related issues – not telling victims how to feel and how to act all the time.

We have to keep the quorum that abuse is wrong, and the victim gets the last word on what that is. We only get to define how to fix things and make it better.

Thank You.

Feeling safe…

Members of PASS recently received an email from president Thomas LaRock reminding us of the importance of proper etiquette and behavior at the summit. The email was prompted by a few unfortunate incidents of harassment, atleast one of which was highlighted by board member Wendy Pastrick as a personal experience. These posts were followed by a lot of discussion on twitter on how much to regulate, what to regulate, do you report or do you not, on and on. There are no simple answers to any of those questions.

The summit is a conference with as much as 30-40 percent of people from overseas – it is enormously hard to enforce very specific standards for behavior. Not to mention that USA is a country with the highest number of freedoms – so there will not be any norm that is totally acceptable to every person. I got a few emails/requests from friends overseas – particularly in Asia and Asia-Pacific region (for the record I was their Regional Mentor for many years) asking for clarity on what constitutes appropriate touch, among other things. A lot of them were just very confused in this regard. I will choose to share what has worked for me personally –
1 Is it ok to hug? If yes, whom?
Generally speaking, yes. I happen to be rather good with body language and I can usually tell when someone is open to it. I am good with my own too and have said more than once that I don’t feel particularly huggy that day. Intuition and body language are good things to keep handy – see if the person looks friendly first, that simple.Even after a decade of conferences there are people I’d rather not talk to – they are not friendly enough for me, I take brush-offs badly even if they don’t intend it, I have enough people I know,and I don’t think anyone is that important to force a conversation. Most trouble ends there.For a hug, just ask. And if it is not your thing it is totally fine to not do it. A courtesy hug is usually one-armed with palm below neck for a brief second or two. It is not a chest-to-chest embrace that lasts a long time. And if you don’t feel it is right for you, just put out your hand for a handshake, most people are absolutely fine with that.

2 What is an inappropriate touch?
Somebody touching you very intentionally, on parts of the body where it is not comfortable. Our bodies have built in wisdom – if someone is violating your boundaries you will know – you will feel flushed,embarassed and violated. If you are not body-wise – check with a good friend if how you feel is appropriate given the situation.

3 When do I report it – do I have to report it?
It is a difficult answer, and like many things in the SQL world – It depends. Reporting has some advantages because the person will not be able to continue the same behavior in public at the conference atleast.Also to remember that you can request anonymity – although chances are high that the perpetrator is going to know it was you. But there are personal cons to this too – depending on how you look at it. It is embarrassing to tell someone you got touched. It is just the way we are, and it is embarrassing for anyone, male or female. A lot of us can deal with it in our own way – I know of one girlfriend who slapped the man’s rear with a lot of force in return(it was effective!), some use odd humor to make the point, some have a frank talk with whoever is doing it. If you are a dark skinned person like me and if it is known you complained it is highly unlikely to make you any more popular, even assuming you are popular. And if you are a foreigner you really don’t want to cause any waves of that nature before leaving. It is all understandable. I would recommend though, to deal with it the best you can and not ignore it – as ignoring it is usually an invitation to the perpetrator that it is encouraged.For those who believe complaining is the route , there are enough valiant men in the community who can shoulder your cause – just approach any of them.

3 People seem to behave in all sorts of ways – sitting on each other’s laps, petting hair, kissing cheeks….how do I know what is the actual norm here?
This was actually a real question posed to me by a friend from overseas this morning – it was accompanied by some pictures of individuals at the summit. The answer to that is just that it is impossible to regulate how groups of people who are in consent with each other behave. If it is personally embarrassing, step away. I have done that multiple times. Where I am from, sitting on someone’s lap is reserved for romantic relationships and almost never done in public. I have no judgements against those who do it but it is too personally embarrassing for me and not something I can think my way out of. In short, ignore them and don’t base any of your own behavior on other people’s that you find difficult to accept as natural.

4 I am a man..is it my job to come to the rescue of any woman if I see her in trouble?
It is old fashioned for a man to jump to a woman’s rescue and many men simply cannot resist it. But physically lashing out against the perpetrator can have many consequences including landing you in jail, if you are not careful. If someone is asking for help, go to their rescue and hold the person via conversation or other means until help arrives. Usually there may be more than one man willing to assist with this if it happens at a community event or party. And no, it is not anyone’s job to protect anyone else, in theory. But it is everyone’s job to watch out and take care of each other.

There are a few more tips I have used that have made my summit experience positive :

1 Always attend parties with someone you know – preferably at least one person who will not be totally drunk and can be relied upon to return with you.
2 Be vigilant and use intuition whenever you are in physical contact with anyone.
3 When in doubt – ask. I have had many experiences at the summit but one that will always be clear and absolute about is that there are a LOT of kind, helpful people. So if complaining is too hard atleast talk to someone about your bad experience. Ask that they not complain if that is not what you want. (Some may not agree to this but there are many who will).
4 Use tips provided by John Morehouse on how to navigate crowds.
5 The summit is a very safe place and a great event to attend, don’t let this discussion lead you into thinking otherwise.

Happy Thanksgiving to all!

Dedicated Admin Connection (DAC)

What is DAC?
DAC is short for Dedicated Administrator connection to SQL Server – it is used for troubleshoooting by those who are SAs in for exceptional situations where a regular login will not meet the needs.

When is DAC typically needed?
In situations that require serious troubleshooting by an administrator.

How do I connect to SQL Server using DAC?
I decided to figure this out the hard way instead of googling for solutions. I only knew that i had to use admin:mylogin on ssms (I picked the ssms option over SQLCMD which is also another way to try) and decided to try it. I clicked on object explorer, prefixed my login with ‘Admin:’ and connected. After all, all connections go via Object Explorer right? No…I was faced with error as below –

dac6

I was using the wrong method to connect – the right way is to use ‘Database Engine’ query icon, the one to the right of ‘New Query’ on SSMS toolbar. I tried again using that method (it gives you the same login window as object explorer did). Below was the next error.

dac5
Now I realized that I had RDP-ed into the machine and did not have remote admin connections enabled. So the next step was enabling those connections, as below. This configuration is apparently needed on clustered servers also – and may not be needed if you are on a local machine.

EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE
GO

Now I was done and connected fine.

What can I do with DAC?
I was able to do a sp_who2, kill a runaway process, and run some dmv queries. I was not able to do a backup/restore. This link from MSDN has a list of diagnostic and troubleshooting queries that can be run (various DBCC commands and querying DMVs). It is important to remember that it not the best option to run regular maintenence – only DBCC commands that relate to troubleshooting specific and rare issues.

What is the database I connect to with DAC?
It is the default database associated with the login. You can access any database after connecting with the use statement. To connect directly to the master you can use the sqlcmd line sqlcmd –A –d master (it assumes your windows login has access to master as SA).

What are some other miscellaneous facts to know regarding DAC?
It has limited resources, so should always be used for lightweight operations.
It cannot make use of parellelism.
The port number that DAC listens on is written to the error log during start up and is 1434 by default.
You can have only one DAC connection open at a time.(Even if the
other admin is a different person).

There is a great post by my friend and SQL Server MVP Kendra Little’s blog on the same subject – which is also a worthy read. She also gives us an interesting DMV based query to find out if there are other DAC users if you are not allowed to get in.

Knowing about DAC might save your bacon in unexpected situations.I consider enabling it to be added to my list of standards for configuring a server.

TSQL Tuesday #72 – Data Modeling gone wrong

My first TSQL Tuesday post on the new blog is an invite by my friend and sql sister Mickey Stuewe (b|t). It is regarding data modeling practices that are well avoided and how problems rising as a result of bad data models can be fixed.

I have several experiences to recount in this regard as my first ever job was with doing data modeling and design. I will pick one of them for this post. Several years ago, I was working for a startup company as a junior DBA. DBAs also had their say in how data models were designed, although we didn’t always get the final word on it. Since it was a new company working off its own software we had some freedom to design things as we wanted to. The issue we had on had was to do with some code tables. Or rather, a code table. We had tables for products, customers and invoices. Now, we had an issue with some other aspects of the business – we needed a few lookup tables. We had a product type, a customer type and an invoice type. In addition to that, we needed payment type, address type and a whole of other ‘types’. The structure of these were more or less similar – a code and a description, in most cases. In a truly 3rd normal design – each of these would have been their own table with a foreign key relationship into the table it was used in. Small tables, no doubt, but specifically intended to store the lookup data in question.The senior DBA to whom I was reporting to had an opinion that a lot of small tables were adding up to the schema getting large and therefore difficult to manage, and that the queries would get too ‘deep’ over time. So he made a call that we would have a generic ‘code table’. A simple denormalized table with three fields – a code, a code type, and a code description. All of our codes – product type, customer type, invoice type, address type – everything would go in that one table, with the code type telling us what it was, and a code description giving some additional information.Programmers were thrilled with the idea, since it meant they had only one table to work with for most of their queries. Initially, this code table only contained about a 100 or so records and seemed like a workable idea. But the business started to grow, and along with it the table too. We started running into issues as below:

1 Almost all the queries in the application were referring to this one table – leading to it getting hit too many times.
2 The queries got increasingly cryptic when they tried to refer to one specific range of records – for example, an address type was indicated by a code A, and their query would say ‘code_type = ‘A”..another query would have code type = ‘I’ and so on..when the programmer left nobody really knew what these letters meant. And yes, there was no third table to decode the meaning.
3 The business wanted to manage the data, and there was no easy way to give them an interface. The data was a jumble of many different things and could not be managed with one screen. The programming effort needed to give them multiple screens was huge.
4 Some of the ‘codes’ started needing additional attributes – phone number type, for example, was initially just cell phone or residential.Then, some customers started asking for a second residential line, so you had to say residential 1 or 2, or cell phone 1 or 2.
5 There were no foreign key relationships defined – so the dependency was on code to enforce the relationship. Code had bugs, and that resulted in bad data getting in very often.

In short, it got so very complicated that the CIO had to approve a project to normalize the whole thing. The rewrite involved creating several smaller tables, putting in the right foreign keys and relationships, and creating interfaces for the business to manage the data. The big lesson that came out of it was that the size of the schema or fewer tables do not really mean a simpler/easier to manager design, in fact it can be the exact opposite.

Good database design is the backbone of a well designed and well performing application – always try to learn what goes into it. A great article by SQL Server MVP Louis Davidson describes several issues like this and is worth reading.

From #23 to #403 – 8 years of SQL Saturday

“Many of us are more capable than some of us, but none of us is as capable as all of us.” Tom Wilson

SQL Saturday 403 marks 8 contiguous years of SQL Saturdays in Louisville – which I have had the pleasure of running. I have learnt many, many lessons during these 8 years – lessons on how to run an event on a budget and unpredictable funding, logistics around finding restaurants/caterers, what audience looks for, what vendors look for, when is the right time and season for an event, who are the right speakers to pick, on and on. But the most important lesson among them all is one of team spirit. Good teams are self managing, where members enjoy what they do,thrive in each other’s company and do it for the love over anything else. I am fortunate to have been blessed with such a team to run our SQL Saturday.

A SQL Saturday team needs to have many skills – we need people who are good at marketing and selling the event to vendors and attendees, we need people who can order food/suggest restaurants/caterers etc, we need people who can do registration and structured work, and we need those invaluable hands who can do just about anything as needed. My team has been with me for 7 years – in the course of which we have lost and gained several people. I lost the person who did our marketing last year, which was one reason why our funding as well as attendance suffered. I was looking for someone with this talent to come on board – when I heard of John Morehouse moving to our town. I had heard of John’s passion for community and the great show they put up at Omaha sql Saturday and invited him onto our team immediately. Along with John we also had Chris Yates – who has been a twitter friend and community enthusiast for a while. Between John and Chris our marketing rocked the attendance and funding like nothing else before. Some of the highlights of our event this year are as below –

1 We had 254 who registered – about 220 showed up. The registration is the highest to date.
2 Our funding was also highest to date and possibly the highest one has for events of our size.
3 Our signage as always was done very professionally by long time volunteer Deana Ritter. The flutter sign in particular has become a cheerful landmark of our yearly events and one that is unfailingly appreciated by many people. Deana and her assistant volunteer Bill Murray get on the streets as early as 5 30 AM to put out signs near the highway for attendees coming in to the event.
4 Our sign ins and registration was ably handled by long time volunteer Karen Schuler. Karen is a cheerful presence for every attendee who walks in the door (directing those without speedpass to ‘table of shame’ :)). Speedpass was hugely successful this year with only around 25 people needing printouts at the venue.
5 Our lunch was catered by Mark’s Feed Store – it was hot bbq lunch and greatly appreciated by attendees. Volunteers James King and Bill Murray worked very hard to get arrangements in place to ensure smooth serving, no spillage or damage to carpet in the venue, as well as a cost effective arrangement to suit our budget.
6 All our swag was delivered to long time volunteer Dave Ingram, who carefully brought in every single package without a murmur (and there were many!!).
7 Our snacks, sodas, ice etc were ably taken care of by speaker cum volunteer Kenney Snell.
8 Our event cameraman was TJ Crivits – who has captured the event in many memorable shots. Personally I see myself going through these when am old and still thinking of what fun it was doing all this. TJ also did a great job with getting the lunch leftovers carted away efficiently to a local soup kitchen.
9 Last but not the least is our volunteer cum speaker cum chapter lead Dave Fackler – whose many mailings helped immensely with publicity to the event. Dave also manned the table for PASS at the event.

During the 8 years of doing SQL Saturdays – we have had great events, good events and events which are kinda okay. We’ve had food deliveries that were missed, key volunteers who could not make it at the very last minute because of personal or work issues, venues that threatened to cancel on us because of weather (one day before, and yes that was our last and will be our last winter event, ever!) , vendors who backed out on sending funds..on and on. This event was one that did not have any such issue , and was a complete, total success. I am proud and happy to close it out and look forward to event #9, and then soon, a whole decade of SQL Saturdays!!

Links related to our event:
1 Why we speak and volunteer for SQL Saturdays – by Dave Fackler.
2 SQL Sat 403 – Recap by Chris Yates
and one of the best geeky examples of using Powershell to tweet event info –
3 SQL Saturday Speaker Marketing with Powershell – by John Morehouse

Detours

When I was new to driving in the States, one of the signs that would make me end up with a panic attack was the ‘Detour’ sign. I grew up in a community that was largely pedestrian, and I developed my road sense mostly by walking. If I walk down a street – chances are pretty high that I will remember that street for some time. Many americans have the same kind of road sense with driving. I was not born here, I learnt to drive much later in life and did not have that. So, in the pre GPS days – whenever a sign appeared that said ‘Detour’ – I would have a panic attack. I did not know the alternate road and where it joined up with the one I was on. I did not know where it would take me and how I would find my way back to where I was supposed to go. But the detour signs did not stop appearing because I did not like them. I had to find a way to develop a better road sense – it would never be as evolved as the one I grew up with but I needed it for sure.

As far as career choices go – I had similar issue a couple of years ago. I had quit my stable DBA job which I had for 6 years and made a move to another position in a company that was being bought out. The writing was on the wall that many of us would be let go soon and I had to find something else quickly. In addition to that – I was in a serious car accident and suffering PTSD which was causing severe insomnia, amongst various other issues. I had no energy to get acclamatised to a completely new and stressful work environment. So I went with what my heart told me, picked up the phone and called a friend from the company I had left. He was not a SQL guy, he managed the windows team there – he had a spot open on his team, for someone who could do Systems Center operations manager, among other things. They had newly acquired SCOM and SCCM and were having a hard time finding experienced help. SCOM is a product closely married to SQL Server – I saw an opportunity to help, and I loved the people I was going to be working with. So I took the job, and worked it for a year and a half until I quit a week ago for another promising DBA position. There were many things I learnt during this career detour –
1 Most SQL folks I know suffer a sort of a paranoid attachment to their work – ‘I can only do SQL Server and nothing else’. Even the mention of Oracle or MySQL is intimidating and is met with ‘no no, not my area of interest and so on’. Some of the fear is valid – Oracle for example is not an easy product to learn and there is no point pretending you can just learn it as you go and get somewhere with it. But some of it is just resistance to change. There are many products married to SQL Server – like SCOM. Or SharePoint. There are many other database products that are relatively simple to learn – like MySQL. So it is important to look at our resistance to branching out into learning other stuff.

2 My good friend Brent Ozar often says that it takes upto 10,000 hours of dedicated learning to learn anything and get to expert level. I learnt from him not to have that expectation to begin with unless I had the luxury of that kind of time – it was important because setting goals too high and then failing was sort of a self sabotage too that was hindering my growth at many levels. I developed some skills with SCOM – by no means expert level but I learnt good scripting on vbscript and a basic level on Powershell. I also learnt a lot of things around monitoring – including the fact that generic monitoring tools can do certain things and we need deeper monitoring tools mostly for other things. Also to develop dashboards and metrics around mining a monitoring database – and how useful that can be to upper management if they are willing to exploit it.

3 I kept a constant eye though on my main area of expertise – SQL Server and database technologies. I kept up my learning with viewing courses on Pluralsight, PASS Summit recordings and attending sql Saturdays/user group meetings. I learnt after a certain period of time that becoming a SCOM expert was not something I really wanted to do and got back on track with SQL Server again.

By far the most valuable lesson I learnt though – was similar to the one I learnt on the road. Not to fear detours. They can and they will happen. To develop a ‘road sense’ of where I am, and where am going, and how my path will sync up to the one I was on. It has left me with renewed confidence in myself and my ability to handle life as it comes.

Finding most used alerts and monitors to override

I had a requirement from one of the teams that uses SCOM monitoring – the requirement was to change alerting level on scom alerts from critical to warning for all servers that were not on production for BizTalk management pack. Now there are several rules and monitors related to this management pack – or in general any one management pack – there is no quick and easy way to override all of them. To do so manually would be very cumbersome and time consuming. Instead – a better way to approach this would be to find rules and monitors that generate the most alerts and override them instead. This would create the override for most relevant rules and monitors – the others can be overridden as and when they happen. The query I used to find most used rules and monitors is as below. I wanted the BizTalk management pack – this can be substituted with any other as necessary.

Use OperationsManagerDW

SELECT

alt.AlertName,

COUNT(*) AS AlertRepeated

FROM Alert.vAlertResolutionState AS ars INNER JOIN

Alert.vAlertDetail AS adt ON ars.AlertGuid=adt.AlertGuid INNER JOIN

Alert.vAlert AS alt ON ars.AlertGuid=alt.AlertGuid INNER JOIN

vManagedEntity ON alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId

WHERE

alt.AlertName like ‘%biztalk%’

GROUP BY alt.AlertName

ORDER BY count(*) desc

TSQL Tuesday 66 : Monitoring

This TSQL Tuesday post hosted by Cathrine Wilhelmson has to do with Monitoring.

During my many years as a DBA i have had a fair share of experience with monitoring tools – some of them as listed:

1 Spotlight for SQL Server – great tool for server level monitoring with very cool graphics. What i really liked about spotlight was that it was so very non-techie friendly – so whenever the director or VP chose to walk by and wants a peek into server health – all I needed to do was to pull up the screen and show him the dials whizzing around. On the flip side, spotlight did not help a whole lot with query tuning. It was also rather difficult to integrate with our ticketing system to generate tickets.

2 More recently, I have been working with Systems Center Operations Manager as our enterprise wide monitoring system. SCOM is a really complicated monitoring system to work with – but once you get familiar with it there is a lot it can do – in particular what I really liked was its ability to open tickets on its own within our ticketing system(with some customization of course), as well as close alerts on its own too when the alerting situation was resolved. SCOM calls for a dedicated admin (which is part of my job), and a lot of tuning to get it down to where it only alerts on what you need without a whole lot of extra noise. SCOM is also NOT a tuning tool – it is only an alerting tool.

4 There are some situations that fall completely outside the realm of third party monitoring tools- for example, we need an alert whenever a user is added as a server role on some servers. We had to write a really simple script that would pull people who are on server roles, set it up as a job to email us every day in case something changed.

**Beginning of script – authored by Mala Mahadevan

SELECT role.name AS Name, member.name AS MemberName,
CASE WHEN (role.is_disabled) = 1 THEN ‘DISABLED’ WHEN (role.is_disabled) = 0 THEN ‘ENABLED’ END as ‘Status’

INTO #temp_dbo
FROM sys.server_role_members

JOIN sys.server_principals AS role

ON sys.server_role_members.role_principal_id = role.principal_id

JOIN sys.server_principals AS member

ON sys.server_role_members.member_principal_id = member.principal_id AND

–filtering for logins that come from our domain
member.name LIKE ‘prod%’

–Format email

SET @tableHTML = N'<H1>Server Role members on Server A</H1>’ +

N'<table border=”1″>’ +

N'<tr><th>Name</th><th>Role</th><th>Status</th>’

CAST ((

SELECT td = CAST(([Name]) AS nvarchar(100)),”,

td = CAST(([Membername]) AS nvarchar(100)),”,

td = CAST(([Status]) AS nvarchar(100)),”

FROM #temp_DBO

ORDER BY name

FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX) ) + N'</table>’
SET @Results = isnull(@tableHTML, ”)

–Email the team on findings
EXEC msdb.dbo.sp_send_dbmail

@recipients =

‘mydbateam@mydba.com’,

@subject =

‘Server Roles on Server A’, @importance = ‘Normal’, @body =

@Results, @body_format = ‘HTML’

DROP TABLE #temp_DBO