Over at http://www.EllaBing.com they have a bow tie dedicated to us SQL DBA’s! Now how cool is that! They call it the Fragmented Index a fitting name if I do say so myself. Check it out!
#Free #SQLServer Training from #StanfordU
Back in my glory years of high school, I wasn’t nearly smart enough to sniff in Stanford Universities direction. I doubt that I am today, but I digress, here is your chance to live it up like all the smart kids! For anyone who may be an accidental DBA, just getting into the field, or who have dreams and aspirations of being a glorious SQL DBA. Of course a good foundation is always important. These free courses are exactly that, an excellent foundation! Last year I was lucky enough to find this FREE course offered by Stanford University, taught by Professor Jennifer Widom, whose curriculum draws from Stanford’s popular Introduction to Databases course. Each topic is covered in videos, with demonstrations, and vary anywhere from 5-30 mins in length. If my math serves me correctly (it rarely does) there are approx 55 videos to watch! Do yourself a favor and invest some time into yourself, watch and enjoy!
Pre-Con #SQLSAT130 Notes w/ @kekline
Today I attended Kevin Kline’s [blog|Twitter] Pre-Con for SQL Saturday 130 in Jacksonville. Below are my unedited, raw notes from the seminar.
Troubleshooting & Performance Tuning Microsoft SQL Server
http://bit.ly/kekline – scripts galore!
This session is about finding problems, and then acting upon it!
– Monitoring
-Benefits of Benchmarking and Baselining
-Preventative Maintenance
**Where do DB problems come from? Hardware Problems 5%; Design Problems 15%; SQL Code Problems 80%
**Rocks, Gravel and Sand Method
Check the stupid stuff FIRST – EVERY TIME!
Process of elimination provides a strong, linear curve of fixes
The phone is not a good indicator of customer satisfaction – not being proactive!
Use quantitative, real information, actionable information
First place to check: Error Logs, save time first! Windows Application Event Viewer, then SQL Server Error Log and SQL Server Agent Log.
**Acting Upon Error Logs
Error Message
Severity Level 1-18 Informational, about 20 serious problem
What happens if there is no error message?
SQL Server Agent – New Agent – Configure agent to notify alerts over level 18-25
Coffee Break!
sqlcmd Utility
:connect server_name
download PowerGui for a GUI based powershell app
Windows Performance Monitor – Benefits: Shows the rate of resource consumption or activity in a wide variety of areas of the server
- Disk IO; Memory; Network
- SQL Server activity – locking, blocking, and deadlocking
PerfMon – DMV sys.dm_os_performance_counters
Select cntr_value
from sys.dm_os_performance_counters
where object_name = ‘sqlserver:buffer manager’
and counter_name = ‘page life expectancy’
How busy are my CPUs? System Processor Queue Length – Very Useful!
Disk I/O probably the number one bottle neck for SQL Server
W/ SSD Drives, Fragmentation is going to be a thing of the past – because with traditional HDs the disk appeture must move to get data, when data becomes fragmentated. W/ SSD this no longer occurs.
Check the following perfmon counters: logical disk – avg. disk sec reads/writes
we want these to be <5ms on a transaction log drive and <10ms on a data file drive
-10-20ms is generally acceptable
->20ms is considered to be unacceptable
Most people forget about TempDB
TempDB is a mjor source of contention on many multi-database system
Run PerfMon counters against TempDB as you would any other production database
Fill Factor – sp_configure set to say 70% to avoid Page Splits: Number of 8k pages that filled and split into two new pages. Should be <20 per 100 batch requests/sec
Creating a Fill Factor of 70% would cause 30% more pages – causing more/higher I/O – something to note.
CodePlex – SQL Server 2008 Extended Events SSMS addin
http://www.extendedeventmanager.codeplex.com
**Server-side traces/SQL Profiler
Warning, Profiler can be overwhelmed by a high throughput
Server-side trace = GUI-less
Saving Profiler to a file allows you to import into PerfMon – then you can visually see when the CPU Spikes, you can see what script is running to cause it
Start following these blogs:
Glenn Berry
Lunch!
**What About DMVs?
select * from sys.dm_os_performance_counters
Examples might include:
Network Interface – Bytes Total/Sec
Processor – % Processor Time
SQLServer: Databases Application Database – Batch Requests/Sec
-General Statisitc – User Connections
-Latches – Average Latch Wait Time
-Locks Average Wait Time
What is a Latch? Short term, light weight locks on specific addresses in memory. IF you see a lot of Latches, you may have memory issues. It doesnt mean you dont have enough memory, just something is wrong with it.
Troubleshooting Sieve – Perfmon, SQL Profiler and Wait Stats
Wait Stats, its all about bottlenecks
Wait Stats – analogy a drive thru window. Think of a CPU as the drive thru window. A process is assigned a SPID.
SQL is waiting to services the process – goes into suspended (Resource Waits)
Runnable (Signal Waits) process ready to be run
Statuses a SPID will have, Running, Sleeping, Suspended, SPIDS reserved for 50 and under are system related.
SQL SERVER 2k5 has 230 Wait Types
SQL SERVER 2k8 has 475 Wait Types
sys.dm_os_wait_stats
-wait information
-cumulative by wait type
-persistent data
-transient data
Wait stat values can be cleared with: (w/0 restarting sql server)
dbcc sqlperf (‘sys.dm_os_wait_stats’, clear);
sys.dm_os_waiting_tasks (how much time has a SPID been waiting and why?)
PAGELATCH_SH (Shared) or IX (Intent) EX (Exclusive)
LATCH_XX commonly arise from contention on resources other than buffer pool, especially due to heaps or text data types
CXPACKET – Query parallelism due to splitting and merging overhead – how much time SQL takes to split threads across multiple CPU/CPU Cores
MAX DOP (Degrees of Parallelism) default is 0 set in sp_configure which means -1 cpu
COST THRESHOLD for Parallelism
**IO Bottlenecks
WRITELOG – writing transaction to the log on disk
PAGEIOLATCH_XX – respresent memory to disk transfers
IO_COMPLETION – Awaiting I/O task completion
SLEEP_BPOOL_FLUSH – Checkpoint IO throttling
select * from sys.dm_db_index_usage_stats – find out good information about indexes
http://www.SQLBLOG.com – Adam Machanic sp_whoisactive
**Storage Quick Check
sys.dm_io_virtual_file_stat – which of the files are getting used – how fast
sys.dm_io_pending_io_requests – who is waiting to get work done?
RAID10 will provide the best performance and availability
backups are single threaded, cannot take advantage of Parallelism
sys.dm_exec_query_stats – what is in the cache and what resources are being consumed
http://www.SQLBits.com – Disk IO Tuning for MS SQL SERVER Newbie
Raid5 is good with Reads
W/ Raid5 three disks required, one disk dies, we are good.
Raid1 two disks, one for one (Mirrored) one disk dies, not good
Raid10 one plus 0, mirrored and stripped
Disk Sector Alignment can yield up to 40% improvement in some scenarios on pre-w2k systems Google Jimmy May – Disk Sector Alignment
SET NOCOUNT ON – allows you to suppress the message for all subsequent transactions in your session, until you issue the SET NOCOUNT OFF command
Make this your first line of code in any stored procedure
9999 rows into the sales table
finished in 5.1 seconds (5176 milliseconds) with SET NOCOUNT OFF
finished in 1.6 with SET NOCOUNT ON
Select …
From…
Where x = ? OR y = ?
This query was taking 29% of the cost
replaced y = ? w/ UNION ALL and broke one query into two. reduced cost
**Controlling and Monitoring Plan Caching
Warm Cache and Cold Cache Testing
DBCC FreeProcCache – Warm Cache Test
DBCC FlushProcInDB(dbid)
DBCC DropCleanBuffers – Cold Cache Testing
When Query Tuning, Watch out for…
-Execution plan red flags, such as: bookmark looups, key lookups and rid lookups
-table spools
-table scan (When a useful index exists)
-parallelization operations
SQLSentry – free tool Plan Explorer good alternative to Execution Plan
Non-Clustered index constructed in a B-tree
Clustered index –
Concatinated Indexes are column specific
LName, Fname, emp-id
where lname = ‘white’ good
where fname = ‘allan’ bad (cannot use index because it is a concatinated index and the lname column needs to be referenced first)
Summary
Is anything broken? Error Logs
Is the problem inside or outside of SQL Server? PerfMon
Whats our Bottleneck inside of SQL Server? Wait Stats
Root-cause Analysis? DMVs
We also covered related topics:
Monitoring
Benefits of benchmarking and baselining
SQL Saturday #130 Pre-Con!
This Friday is the pre-con event for SQL Saturday 130, in Jacksonville, Florida. Myself along with 8-10 other team members will be attending both the pre-con and the SQL Saturday event. I am thoroughly looking forward to seeing a lot of old and new friends. Additionally how could you pass up the chance to get training from Kevin Kline!
Database in Suspect Mode… Frowny Face.
The other day I received a call from a developer telling me that they could not access a particular database in one of our lower environments. After a bit of investigation, I found the database causing the problem, it was in ‘Suspect’ Mode. Here is what I did to correct the issue.
ALTER DATABASE DatabaseName SET EMERGENCY
GO
ALTER DATABASE DatabaseName SET SINGLE_USER
GO
DBCC CheckDB (DatabaseName , REPAIR)
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO
Presto, the database was now fully functional and accessible. A very quick fix, to what had the potential to really ruining my Tuesday morning.
Cheerio!
Something simple I find useful!
SQL TEMPLATES!
Somehow I missed this. CTRL + ALT + T, go ahead press it. Wammo! All these lovely templates, to help us out! Templates, yeah nice, convenient, yes. But, what I like best, is you can add your own scripts! For as long as I can remember, I wondered where do DBA’s keep all their scripts? File Share? Bah, in my mind inconvenient, but alas, I delt with it, because I didn’t know any better. Not now my friends! I am slowly but surely putting all of my custom scripts into SQL Templates, and not looking back! A copy of all templates are placed in your user Documents and Settings folder under Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates.
Cheerio!
The DBA Formally Known as @Brentk81
Changing my Twitter handle has been in the back of my mind for some time now. brentk81 just didn’t have the charm, pizazz or descriptiveness I was looking for. In addition to the fact that my blog goes by the name of http://www.sqlrook.wordpress.com, I almost felt as if I had two separate personas I was trying to manage. One being this guy named Brent, trying to tweet about quasi relevant stuff, and the other being SQLRook, where I try my best to blog about SQL Server. But ENOUGH is ENOUGH! I’ve had it! I can’t take this much disorganization in my life! [JK] But still it was annoying. So I took the plunge breaking all the twitter rules of changing my handle name. I risked being ostracized by my piers…I risked it all for the glory of a name! And well, it was pretty unassuming, and it just made sense to have my Twitter account and Blog be known by the same name. Only one person on twitter even noticed… Jes Schultz Borland [Twitter | Blog], Thanks Jes! Oh, and I changed my pic…just keeping you folks on your toes!
Cheerio!
Getting My Training On!
This year has started off a little slow with personal development. I’ve been doing a lot more traveling with my new job that I originally did not anticipate, plus I’m still getting acclimated to my new city of Jacksonville, but I digress, enough with the excuses. So to jump back on the wagon, I recently signed up for two training opportunities in my area. The first being the SQL Server 2012 Special Ops Tour and the second being the PreCon at SQL Saturday #130 with the one and only Kevin Kline [Twitter| Blog]. The Special Ops Tour is *FREE* so there is no way I could pass that up, and the PreCon is a *CHEAP* 99 bucks with the early bird, small potatoes when you consider the plethora of knowledge that you will walk away with after a full day of training. Not to mention the SQL Saturday event the following day!
I have yet to make it to a Jacksonville User Group [JSSUG ] meeting, but I’m looking to go to my first one tomorrow. Bradley Ball [Twitter | Blog] is presenting on…. Errrrr….Scratch that it appears there has been a change! Mike Davis [Twitter | Blog] who I am now following, is now presenting on New SSIS Features of 2012…#Sweet!
Cheerio!
Dropping a user attached to a database schema
So today I was attempting to delete a SQL account from one of my servers. Easy enough I thought, deleting the account from the server, check. I go to delete the account at the database level and BAM! I get the all too familiar evil red script!
Error: 15138 The database principal owns a schema in the database, and cannot be dropped.
So whats the error actually mean? Jump over to Derek Dieter’s awesome site SQL Server Planet to find out! I followed his script verbatim and it worked great for me!
Back on the Internet Baby!
First! I finally got the ‘interwebs’ at my new place in Jacksonville. I went about a week and a half without TV or Internet, phew that was rough…but not all bad, it just gave me the time to read more SQL books! This first post is about two weeks old, and will be my first post in a series of catch-up posts.
Another long week comes to an end. I’m sitting on a plane while writing this blog post, flying from Minnesota to Atlanta, then finally home to Jacksonville. With another disaster recovery test under my belt, I’m gaining more and more confidence in my abilities. This time I was able to meet my entire DBA team in Minnesota, it was great to finally put names with faces. I was finally able to get my hands on our replication schemes and get some good QA time in with the more Senior DBAs. I also greatly enjoyed being able to interact with the other DBAs on a more personal level. I was also able to meet most of the Server, SAN, and development teams. Project Managers, and Business Analysts, everyone that goes into a successful DR practice. Everyone from every team involved put in many long hours; it was a great experience, and an overall success. I’m greatly looking forward to our next exercise in February.
SQLRook 