July 22, 2014 at 9:37 am
New Born DBA (7/22/2014)
Grant Fritchey (7/22/2014)
Learn PowerShell.Automate all the things.
Would love to, but where should I start?
My preferred PowerShell instructor, for just PowerShell itself, is Don Jones. He has a number of books and lesson plans, etc. For getting into SQL Server and PowerShell, I'd suggest Allen White.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2014 at 10:18 am
New Born DBA (7/22/2014)1) I haven't set up any alerts on the system since all the alerts are in place?(I am hoping that they are in place, but have to figure out how to check them)
One of my slide titles for my SQL corruption session is "Don't assume". There's your work for this week. Alerts. SQL Agent alerts first, other sensible ones later. There are probably the bear minimum of alerts.
2) Automated restore. Again, will have to learn how to do that.
There's your work for next week
3) Job monitoring. You got me there, but I am going to start working on things that you guys have told me.
And there's your work for August. 🙂
Unfortunately, everybody has sa priv which does surprise me, but I cant change that since I have already talked to my manager and he likes it the way it is.
It's your job to explain to him just how dangerous that is. There are enough examples of companies who no longer exist because some disgruntled employee with too many permissions deleted everything. A SQL MVP, just the other week, found a timebomb job (drop database on a certain date) from just that kind of thing.
Do you have performance benchmarks? If a user says 'This query is slow today', can you tell how long it ran last week as a comparison?
I am sure there are monitoring tools available to capture data, but we don't use any monitoring tools.
I said nothing about monitoring tools, I asked if you had benchmarks. There's your work for the rest of August and September and possibly October)
Basically, you're being reactive. Waiting for a problem. That's fine when it's a small system and a quiet time, bu it's inadequate anywhere else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2014 at 10:25 am
GilaMonster (7/22/2014)
New Born DBA (7/22/2014)1) I haven't set up any alerts on the system since all the alerts are in place?(I am hoping that they are in place, but have to figure out how to check them)
One of my slide titles for my SQL corruption session is "Don't assume". There's your work for this week. Alerts. SQL Agent alerts first, other sensible ones later. There are probably the bear minimum of alerts.
2) Automated restore. Again, will have to learn how to do that.
There's your work for next week
3) Job monitoring. You got me there, but I am going to start working on things that you guys have told me.
And there's your work for August. 🙂
Unfortunately, everybody has sa priv which does surprise me, but I cant change that since I have already talked to my manager and he likes it the way it is.
It's your job to explain to him just how dangerous that is. There are enough examples of companies who no longer exist because some disgruntled employee with too many permissions deleted everything. A SQL MVP, just the other week, found a timebomb job (drop database on a certain date) from just that kind of thing.
Do you have performance benchmarks? If a user says 'This query is slow today', can you tell how long it ran last week as a comparison?
I am sure there are monitoring tools available to capture data, but we don't use any monitoring tools.
I said nothing about monitoring tools, I asked if you had benchmarks. There's your work for the rest of August and September and possibly October)
Basically, you're being reactive. Waiting for a problem. That's fine when it's a small system and a quiet time, bu it's inadequate anywhere else.
Thanks! I am glad that I started this thread otherwise I would have been sitting at my desk thinking about "what should I do?". I appreciate it a lot.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 22, 2014 at 10:34 am
I have also done some stuff to improve performance by creating appropriate indexes and by changing the way queries are written.
I think that's one of the most value-added and differentiated tasks a DBA can and should do. But it's often not done, done all too rarely and/or done too poorly.
So, specifically? (numbered for ease of reference during discussion):
1) Which do you review first, indexes or the way queries are written?
2) What do you look for in written queries as an automatic "red flag" for further review/adjustment?
3A) How do you review indexes for a table?
3B) Say you determine an index rebuild is needed. Can you do it live? Can you schedule it for a specific time?
3C) Say you determine a different clustered index is needed on a given table. What steps do you go through to implement that change?
Similarly, if you can't answer this q:
4) What are the differences among hash, loop and merge joins?
Then there's something else you can spend time on :-D.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 22, 2014 at 11:24 am
ScottPletcher (7/22/2014)
I have also done some stuff to improve performance by creating appropriate indexes and by changing the way queries are written.
I think that's one of the most value-added and differentiated tasks a DBA can and should do. But it's often not done, done all too rarely and/or done too poorly.
So, specifically? (numbered for ease of reference during discussion):
1) Which do you review first, indexes or the way queries are written?
2) What do you look for in written queries as an automatic "red flag" for further review/adjustment?
3A) How do you review indexes for a table?
3B) Say you determine an index rebuild is needed. Can you do it live? Can you schedule it for a specific time?
3C) Say you determine a different clustered index is needed on a given table. What steps do you go through to implement that change?
Similarly, if you can't answer this q:
4) What are the differences among hash, loop and merge joins?
Then there's something else you can spend time on :-D.
Since I don't have much TSQL knowledge, it is hard to explain what I review first, but I usually run the query and see how much data it is fetching. Are there any joins, are there any wildcard%, or any function it's calling etc. Then I look at the execution plan to see if there is Index SCAN or SEEK or if there are any lookups. I also look at the statistics and get some idea on how many logical reads there are, how many scans. That's pretty much it for me. I don't have nay table at the moment which is heap, but let's say if I do, I won't know which column to create clustered index on, but maybe I will target columns with int values.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 22, 2014 at 11:54 am
Also not being snarky, but I've found that the reason a lot of people turn to PowerShell is because they don't actually know what they're doing in SQL Server. For example, I've seen many a script for having PoSH do all backups for the entire enterprise from a central location. While that sounds fine, that makes the enterprise reliant on a single point of failure. It would be far better to use PoSH to distribute and activate standalone backup routines to the individual systems so that there is no single point of failure for this most important activity.
SQL Server is pretty good at what it does and can do especially when you get a little T-SQL involved. To coin a phrase, "Just because you can do something in PowerShell, doesn't mean you should". See the sister-phrase to that in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2014 at 12:23 pm
Jeff Moden (7/22/2014)
Also not being snarky, but I've found that the reason a lot of people turn to PowerShell is because they don't actually know what they're doing in SQL Server. For example, I've seen many a script for having PoSH do all backups for the entire enterprise from a central location. While that sounds fine, that makes the enterprise reliant on a single point of failure. It would be far better to use PoSH to distribute and activate standalone backup routines to the individual systems so that there is no single point of failure for this most important activity.SQL Server is pretty good at what it does and can do especially when you get a little T-SQL involved. To coin a phrase, "Just because you can do something in PowerShell, doesn't mean you should". See the sister-phrase to that in my signature line below.
True enough. But you can say the same thing about T-SQL, just because you can doesn't mean you should. There may be some things that can be done easier in PowerShell than in T-SQL.
We are finally getting to the point of automating much of our ASI (upgrades) processing out here in Afghanistan. What we are doing now during the draw down is developing processes they would have loved to have had in place as they geared up and were sustaining 20+ sites across Afghanistan (and beyond, have a site in England but Afghanistan staff no longer supports it). Things we are developing now are going to be used to make supporting other sites easier and standardized.
One thing I have learned in my reading is that PowerShell 3.0 introduced Workflows and this are restart able over system restarts. A benefit here is if you are doing a system upgrade that requires a reboot, the PowerShell Workflow can pick up at the point of the restart. Pretty cool.
Also, what may be painful in T-SQL may be easier to accomplish in PowerShell just by the nature of the process being automated.
It really comes down to using the right tool for the job.
July 22, 2014 at 12:43 pm
Lynn Pettis (7/22/2014)[hr
It really comes down to using the right tool for the job.
+INFINITY!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2014 at 9:00 am
Great Topic, It helps for many New Born DBAs where there is no guidance what to do other than waiting for problems comes in.
July 23, 2014 at 9:19 am
My advice is get to know what the business currently has set up (databases, data -- the why and wherefore of it, and business rules). Then see if you can find a way to make it work better.
There may be processes where they take 15 steps to get somewhere that SSIS would take them in 2 steps, for instance. Or where (as Jeff's .sig suggests) they are looking at individual records instead of entire sets of records. If you can prove to your boss that you know how to make things more efficient, he will love you for it and take your advice more seriously.
Also, regarding the security issue. Do you have a QA / Dev server that everyone uses? Tell your boss that you're going to run a test (but don't tell him what). Then recruit one of those non-DBA sa account users to start dropping things, entering bad data, creating items that slow down the system. Make sure this recruit doesn't tell anyone what (s)he is doing. Then see how long it takes for people to notice things are hosed up.
Of course, you'll want to back things up before you do this (including system DBs). And DO NOT DO THIS IN PRODUCTION. You don't want to lose your job over it, after all.
July 23, 2014 at 9:03 pm
Lynn Pettis (7/22/2014)
Jeff Moden (7/22/2014)
Also not being snarky, but I've found that the reason a lot of people turn to PowerShell is because they don't actually know what they're doing in SQL Server. For example, I've seen many a script for having PoSH do all backups for the entire enterprise from a central location. While that sounds fine, that makes the enterprise reliant on a single point of failure. It would be far better to use PoSH to distribute and activate standalone backup routines to the individual systems so that there is no single point of failure for this most important activity.SQL Server is pretty good at what it does and can do especially when you get a little T-SQL involved. To coin a phrase, "Just because you can do something in PowerShell, doesn't mean you should". See the sister-phrase to that in my signature line below.
True enough. But you can say the same thing about T-SQL, just because you can doesn't mean you should. There may be some things that can be done easier in PowerShell than in T-SQL.
We are finally getting to the point of automating much of our ASI (upgrades) processing out here in Afghanistan. What we are doing now during the draw down is developing processes they would have loved to have had in place as they geared up and were sustaining 20+ sites across Afghanistan (and beyond, have a site in England but Afghanistan staff no longer supports it). Things we are developing now are going to be used to make supporting other sites easier and standardized.
One thing I have learned in my reading is that PowerShell 3.0 introduced Workflows and this are restart able over system restarts. A benefit here is if you are doing a system upgrade that requires a reboot, the PowerShell Workflow can pick up at the point of the restart. Pretty cool.
Also, what may be painful in T-SQL may be easier to accomplish in PowerShell just by the nature of the process being automated.
It really comes down to using the right tool for the job.
Sounds like a really cool application for PowerShell.
On the other thing about...
True enough. But you can say the same thing about T-SQL, just because you can doesn't mean you should. There may be some things that can be done easier in PowerShell than in T-SQL.
...{snip}...
It really comes down to using the right tool for the job.
... the point that I was trying to make is that a lot of people don't actually know what the "right" tool is nor do they know what the tool called "T-SQL" is actually capable of nor how easily it can solve a wealth of problems. As a result, they end up using the "wrong" tool(s), frequently resulting in the "Tower of Babel" syndrome, because that's all they know. Notice that I put "wrong" in quotes because if you don't know a different tool exists, then the tool you know and have fallen back on will seem like the "right" one.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply