Monday, September 29, 2008
I have several .sql files open in SQL Server Management Studio (SSMS). I'm using SQL Server 2005. I wanted to find all occurrences of a particular table anywhere in my open scripts. Sounds simple, right? Just use the find feature (Edit --> Find or Ctrl-F), enter the table name and choose All Open Documents in the Look In dropdown.
Here's the catch - I'd run some of these scripts, and their Results tabs were active. The search function failed to search the content of those Editor tabs, searching the Results tabs instead!
Come on Microsoft, this is a rookie mistake. I'd understand searching the Results tabs in addition, but certainly not to the exclusion of the Editor tabs!
Friday, April 18, 2008
Friday, April 11, 2008
Friday, March 14, 2008
Registered servers make a great shortcut if you manage more than a few SQL Server instances. I’ve got a quick little tip for you…
First - thanks for stopping by!
Here’s another tool for you...
In SQL Server Management Studio (SSMS) you’ll find several embedded tools. I talked about templates the other day, and today I’m reviewing the Registered Server pane. Ctrl+Alt+G (View – Registered Servers in the menu) will bring this pane into view, and you can dock it as you like. You can set it to auto-hide to save space as well.
Right-click in the pane and one of the options is New, which has two choices:
· Server Group… allows you to create a folder for like servers (more in a minute)
· Server Registration… brings up a modified connection dialog which allows you to specify the server, choose the name, and add descriptive text (this is displayed when you mouse over the server registration – I find it helpful to include some database names here.)
1 – Types: Depending on the services you have installed, you can register various types – Database Engine, Analysis Services, Reporting Services, SQL Server Compact Edition, and so forth.
2 – Groups: You can group servers with a folder structure, similar to templates.
3 – Connections: Once registered, connecting with the Object Explorer or a new query window is quick and easy.
4 – Portability: Other right-click options allow you to export or import the server list to an XML file (more on this in a minute).
Now that my company’s got a foot print on four continents (we recently added Europe and
The ability to export and import server registrations is a nice touch – kudos,
I set up my environment on one machine, exported the entire list (allowing it to store passwords – relax, they’re encrypted!). Then, I copied the file to my other machine and imported. Voila! Now all my servers are at my fingertips whichever keyboard I use. After this, I destroyed the file, exported again without passwords and put a copy in source control. This way I can hand that to other a new DBA one day and simplify that initial setup process.
If you have a new developer starting, and he needs access to only a few instances, simply export only the Development group (or single server) needed. Pass the file to the developer and she’s all set!
And here at willsql4food HQ, I can have personal stuff in other groups for research, home use, and any consulting work I might do.
The more servers you manage, the more you’ll come to appreciate this simple, yet powerful tool in SSMS. Hope this helped you!
Thanks again for visiting,
Sunday, March 2, 2008
Templates are a great addition to any DBA’s toolkit. I’ll walk you through an overview and even throw in some free code – you’ll be more productive by Friday even if today is Friday.
First, thanks for stopping by!
Repetition again? Ugh…
I find that as a DBA, there are several things that are just repetitious. I’ve found a few tricks and tools to speed up my work and remove some of the drudgery. Today, I’ll talk about templates, and cover some of the others soon.
Templates aren’t new in SQL Server 2005 – they’re also available in 2000, and I won’t pretend to know or care about SQL Server 7.0 and previous versions – that’s someone else’s blog! I’m not knocking them, but I’m not using them either.
The strength of templates is that they shorten repetitive but slightly different tasks. One I use frequently, and display at the end of this post, helps me check index usage on a given table. I like to snoop around my indexes occasionally, checking on different tables when mood or suspicion strikes me. Today I may be concerned about dbo.customers, tomorrow it might be sales.orders. Furthermore, I might be investigating problems in the Demo database one minute, Beta the next, and Production when usage peaks after lunch.
I might even find <gasp!> that indexes are different in different databases. (And that might be okay…)
Sure, I could type out most of the code below, but why when templates will for me? Plus, I get quick, consistent answers when management is snooping around me.
So how do you get to these, you ask? I illustrate with SQL Server 2005 Express Management Studio (SSMS) since that’s what’s on my laptop and mention any obvious differences on my work PC.
What and where?
There’s no special file extension for templates – they’re straight SQL text. Templates located in the expected directory behave like templates in Excel, Word and many other tools – opening one, changing and saving triggers a Save As… dialog. This protects the original template file by prompting you to save somewhere else. Where is this expected directory, you ask? Yours will vary on OS, version, install drive, and username, but on my laptop (
C:\Users\Will\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\Templates\Sql
My work desktop (WinXP, SQL2005 Standard):
C:\Documents and Settings\wcarter\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql
Up one directory and you’ll find templates for other installed tools, such as SQL Mobile, Analysis Services, etc. Play with those, blog on them, comment below, or whatever…
To display a list of available templates, the short cut is Ctrl+Alt+T. (View / Template Explorer if you prefer menus). You get a file explorer style dockable window with templates grouped in folders. Right click any one for options like New, Edit, Search, Rename, and Delete (Edit is the way to change the template rather than a single instance of it). You can even add your own folders to stay organized.
Filling in the <Blanks, , >
The power is in the parameters! They take the form of
<Parameter name, Type, Default value>
Put one wherever you have dynamic code. You don’t even have to supply all the values – <P1, , > will be sufficient.
To replace them with specific values, Ctrl+Shift+M (Query / Specify Values for Template Parameters) brings up the Specify Values for Template Parameters window, listing each parameter’s:
· a textbox where you can enter a value. (If you specified a default, it’s already there.)
Especially for templates I don’t frequently use or those I share, I often “name” my parameters with a short phrase or question, like this:
<Table name?, sysname, customers>
<Audit date from:, datetime, dateadd(day, -1, getdate())>
<Audit date to:, datetime, getdate()>
Templates are also great for those occasional, moderately complex operations. Build some templates to handle those customer account credits that can’t be done in your app’s UI; quarterly data archival and purge processes; sales force hierarchy changes; and so on. You won’t have to remember where the file is or if you changed all instances of customer id, sales person name, date, etc; it’s just Ctrl+Alt+T, Ctrl+Shift+M away.
Of course, if you spend much time creating and customizing, find that directory above and back it up. I even keep copies on my thumb drive for when I’m visiting someone else’s cube…
Templates are readily accessible, customizable, and time-saving. They make a great addition to any DBA’s toolkit. Check them out – you’ll be more productive by Friday.
They’ve saved me a lot of keystrokes (so I had some left for this blog!)
Again, thanks for stopping by!
The Promised Example – Enjoy!
TEMPLATE: Snoop Indexes.sql
Finds usage stats for all indexes for a given table
use <db, sysname, WillSQL4Food>
-- Index usage (seeks, scans, updates, etc.)
select object_name(i.object_id) table_name, i.[name] index_name
, c.[name] column_name, ic.key_ordinal, ic.is_included_column
, i.type_desc index_type, i.is_primary_key, i.is_unique, i.is_unique_constraint
, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update
from sys.indexes i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
left join sys.dm_db_index_usage_stats s
on i.object_id = s.object_id
and i.index_id = s.index_id
and s.database_id = db_id('<db, sysname, WillSQL4Food>')
where object_name(i.object_id) = '<tablename, sysname, >'
order by object_name(i.object_id), i.is_primary_key desc, index_name, ic.is_included_column, ic.key_ordinal, c.[name]
select * from sys.dm_db_index_physical_stats(db_id('<db, sysname, WillSQL4Food>'), object_id('<tablename, sysname, >'), null, null, null)
-- Statistics generation
select * from sys.stats s where s.[object_id] = object_id('<tablename, sysname, >')
Friday, February 15, 2008
Multiple-record sets. I'm not talking about MARS; rather queries which are expected to return a single record, but instead occasionally return two or more.
Now say your application has a subsystem for user access control, third party integration, or content management. Your user may be an individual, a group of individuals, or a client's SOAP architecture consuming a web service. Different users have access to different things in your application. These things might be technical analysis articles on NYSE traded stocks; stock transactions; items in a virtual shopping cart; billing invoices or other account activities for your services; or even comments on a blog.
When your controlling subsystem validates the user's right to a particular thing, it likely issues a SQL query against the application's database. It may look something like this:
select u.user_id, ur.user_rights
from users u
join user_rights ur on u.user_id = ur.user_id
where u.user_id = 3245674 and ur.thing_to_access = 'blog';
What your application expects is a single record, getting it in some, even most cases. It may look something like this:
However, what it actually gets, in a few cases, is this:
And worse, sometimes it receives this:
Most trying, as I've said, this doesn't even happen in most cases. This can be the cause for a bug with the classic traits - infrequent, inconsistent, and hard to track down. The bug can be on the data side requiring a DBA to resolve, on the application side requiring a developer to solve, or in the business layer requiring an entire committee, budget, and project plan to solve.
Now you may be scratching your head and wondering how this even leads to a bug. Let me illustrate!
Assume your application expects exactly one record defining user rights, looking up users by id. Now if the user's id is 3245674, there's only only one record to handle making the user rights certain. However, when looking up the user with id 3245675, two records are returned and the rights granted to the user are variable.
This can be bad. Sometimes very bad.
How so? Let's look at the cases:
1 - The code interpreting the recordset may assume a single record, interpreting the first in the set and ignoring the rest. In this case, the user's granted rights depend on the order of the records returned. Without an ORDER BY clause, the first record in subsequent executions of the same query can vary for a number of reasons - page splits, index reorganizations, and statistics recalculations to name a few. The user in this case would sometimes have rights to write articles but not post and at other times rights to edit and post but not write.
2 - It may loop through the records, discarding all but the last record. It should be obvious that this is the same problem as #1.
3 - It may even loop through the set and attempt to synthesize the results into a single set of permissions. In the above case, the user would be able to write and edit; read and approve comments; both of which may break business logic or internal controls. Then comes the ability to post - how do true and false combine? Either way, the end result is the user likely has the incorrect rights.
Now why does this happen, and what do you do about it? If you already have some strategies, add a comment for my other reader.
If not, check back with WillSQL4food soon!