SQL Tact

pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

8/23/2015

Actual Emails: Allow NUNS to Lead You to Good Clustered Index Design

Wrote this email exchange with a colleague who wanted to confirm that the client-proposed design for a table was... less than optimal.


Subject: clustered index
From: A. Developer

Hey William,
I believe the client created this Clustered Index... <horrifying screenshot of a clustered index with many large nvarchar columns as the key>

From: William 

The clustered index is ideally 
1)      Non-changing
2)      Unique
3)      Narrow
4)      Sequential 
“NUNS” 
Having those multiple nvarchar columns in the clustered index is probably not a good idea, as it violates #3 and probably #1 and #4 too.  
The most ideal clustered index is on an integer identity column. You can modify the design of a table to add one of those. 
Look at the data and suggest a new clustered index. That five-column clustered index might be a perfectly fine nonclustered index, but is an inefficient clustered key.

From: A. Developer
That makes sense. Thanks William!

8/20/2015

Your Devs' Questions Answered With sys.dm_server_services

It starts with "I'm a developer...

"... with no RDP access, how can I tell if the SQL Server Agent service is running?"
"... and I don't think CDC is working. Is the SQL Agent on?"
"... how can I tell how long has the SQL Server been running? Did somebody reboot it last night?"
"... so hey I need to know if the SQL Server service set to automatic startup."

Here's an easy script that anyone can use, it can be especially helpful for developers without RDP access to the Windows Server that is hosting the SQL Server service. It'll answer these developer questions and more, in addition to a lot of other utility for you as a DBA.
SELECT  servicename -- Ex: SQL Server (SQL2K8R2)
, startup_type_desc -- Manual, Automatic
,  status_desc -- Running, Stopped, etc.
,  process_id
,  last_startup_time -- datetime
,  service_account
,  filename
,  is_clustered -- Y/N
,  cluster_nodename
FROM   sys.dm_server_services

You don't need to be a member of the sysadmin server role to run this, but you do need VIEW SERVER STATE. Developers can be given this permission, even in production, as it gives them access to many key dynamic management views (DMVs), including DMV's for diagnostics and performance tuning.

MSDN reference: https://technet.microsoft.com/en-us/library/Hh204542(v=SQL.110).aspx

7/19/2015

See you at SQL Saturday Baton Rouge 2015!

SQL Saturday is a global event to bring Information Technology speakers and professionals together for a community-driven, community-attended free day of technical training. The Baton Rouge SQL Saturday 2015 event is produced by the Baton Rouge User Groups, and will be our sixth annual event on campus at LSU. We expect ~600 regional IT professionals and national speakers to join us. 

This free conference is open to the public and is perfect for students, database administrators, .NET developers, business intelligence developer, SharePoint admins and developers, IT managers, server admins, network admins, and job-seekers.



Folks with the following skillsets are drawn to SQL Saturday Baton Rouge because of the professional networking, free training, and giveaways:
  • SQL Server Administrators
  • Business Intelligence Developers
  • Data Analysts
  • ETL Developers
  • C#/VB.NET Developers
  • Mobile Developers
  • Windows Server Admins
  • SharePoint Architects
  • SharePoint Developers
  • Network Administrators
  • Quality Assurance Analysts
  • IT Managers
  • Project Managers
  • Hiring Managers
  • Jobseekers of all levels of experience
  • Students
  • CIO's
  • CEO's

We got some great feedback and testimonials in 2014, like these actual responses:
  • “Some very good talks with great content, large community of very smart, talented developers, great networking, and awesome SWAG/goodies”
  • “Great networking opportunity, good way to get to know the community. Some talks were really excellent presentations on state of the art database techniques.”
  • “Lots of companies with great networking opportunities; lots of free training and free stuff. High quality and free is very unique.”
  • “It was a fun way to network and learn about how everyone is using technology that we can bring back to our own organizations. It is a chance to learn something new, and meet new people.”
  • “My first sql saturday and certainly not my last. Had a very nice time.”
After our hugely successful 2014 event, we sent out an internet survey to all registered attendees. Check out the results of our last, most important question: