Skip to content

2015🔗

  • 1 min read

Model needs exclusive lock

Ran into an issue where a developer was trying to create a database and was denied due to no ability to obtain exclusive lock on model. After verifying with other blogs, I found that creating a database required exclusive lock to use model as a template for the new database creation.

In my case I had connected with SSMS directly to model for some queries instead of master. In this case, SQL Complete (Devarts's excellent alternative to SQL Prompt) was querying the schema had this open session was blocking usage of model to create a new database. After killing this low priority query session, no issues were experienced.

Good to remember! Don't connect directly to model unless you have a specific reason to do so. Otherwise, you might be the culprit on some blocking errors.

  • 6 min read

Documenting Your Database with Diagrams

Don't get too excited. I know you love documentation, and just can't wait to spend some time digging in to document your database thoroughly. I imagine you probably want to build visio charts manually, or draw whiteboard diagrams by hand and take pictures.

For the rest of us that are lazy, a tool to help document your database is a great idea. I'm a big fan of SQL Doc by Red Gate, and ApexSQL Doc . I ended up using ApexSQL doc to document the database at my work, though Red Gates is also a great option. Both provide a great schema documentation tool that can generate a helpful CHM file to browse through (which I much prefer to trying to sort through PDF/WORD docs)

However, there is one thing that I was recently tasked with that made me appreciate a little more hands on documentation. In the case that you are deploying an application or any set of tables/structure that might end up being used by someone else, describing your data model can be a really helpful resource to someone trying to query or implement some custom implementation against your data. This might be helping document some data structures for someone building a report, or even developers trying to leverage some of the data in a separate implementation.

Understanding the data model as a dba/dev is important to being able to architect improvements as well, so I've found that going through and diagramming some of the logical structures/groupings can be a tremendous benefit to better understanding a database that perhaps you didn't architect from scratch, or has legacy results.

Some positives I see:

  1. Forces a better understanding of how your data model is constructed
  2. Can help highlight weaknesses and flaws in your data model
  3. Can help explain the data model to folks who may not be living and breathing your business, so if terminology and fields sometimes are confusing on how things relate, the data model can help clarify some of these things.

Some negatives:

  1. Requires a little elbow grease
  2. You'll find things to fix

Visio

Visio 2010 is the last Visio version that provides the capability to reverse engineer a database into a diagram. This is unfortunate. I worked for a while with this, but ended up shelving Visio as a long term option because of it's unintuitive nature, and behavior at times. For example, refreshing your database schema to identify changes wouldn't help flag any new changes, you'd have tables that were in several diagrams suddenly being given new table names like TableTest, TableTest1, TableTest2, instead of reusing the same table in the model. Also, the layout and arrangement can be pretty messy at times. Working with data sources requires you to use GENERIC OLEDB from what I recall, otherwise I got errors using SQL 2014. Lots of little things that added up to a poor experience. Gave it the old college try.... not worth it in my personal opinion

Visual Paradigm

Visual Paradigm Gallery This tool has some amazing functionality, but unfortunately was very tedious and complex for the purposes of documenting an existing database. I think this aspect of Visual Paradigm's reverse database toolset needs some major productivity and UI tweaks to be usable. It may be great for someone working with diagrams for creating databases and models, but for a SQL dba working to document their database better, it wasn't a great option. Even though very unintuitive, I did like the customization options for layout and the arrangement. The problem was none of the changes performed in realtime, thereby making it an annoying guesswork game. Not a huge fan. Also, very difficult to easily identify tables that had related tables to add with a click, so not easy to navigate all the tables and related tables in an intuitive way. Not a big fan of having to tweak 10 settings via 10 trips to a right click context menu. Their presentation workflow needs some major improvements.

DBVisualizer

DbVisualizer By far the best auto arranging layout tool I found. Amazing. It uses yEd as the backbone of the diagramming, so I also downloaded yEd, but for me to utilize, I'll definitely have to play around as the engine is powerful, but would take programming to get it to do anything similar to what DbVisualizer accomplished. It's more of an exploratory tool than a documenting one, but you can save the graph to .gml format and open in yEd to change. A little extra work, but possible if you like the results. It doesn't display foreign key columns, so it's more of looking at the referring/relationship side of things, without a full column list. I'm pretty much sold that this is bar none the best auto-laid out representation of tables I've ever seen with any tool. I'll be keeping the free DbVisualizer as a tool for reviewing adhoc data models for sure! The negative for documentation purposes is that none of these graphs are saved, so it's good for adhoc or one time, but not to regenerate.

Aquasoft Data Studio

They have a great output, but unfortunately they don't allow you to only show key columns, therefore your diagrams get pretty bloated. It's pricey, has some great other features, and I'd be a big fan, except the diagramming options aren't as robust as the others for customization. If you are documenting a data model, I like to only show key columns (PK/FK) to ensure there is not excessive noise. I did reach out to company on this option, and they said: "You can't show the PK and FK without the entity. So, you can't show them be themselves." The lack of any extra options to submit feedback, or anything as a potential buyer put me off to this product for now.

Other tools that were way too complex and pricey to fit documentation needs

  1. Oracle SQL Developer - very difficult to get the SQL connection up and running. Not unintuitive, not visually pleasing output
  2. Toad Data Modeler - same as above. Not visualizer pleasing, too many steps. Targeted at design rather than documentation.
  3. ERStudio... didn't even consider, much too pricey for documentation purposes
  4. SQL Architect - much too unintuitive and complex

Final Winner: DbSchema

DBSchema This is the winner. I switched all our diagrams over to this tool. A few major pros:

  1. All diagrams saved as "layouts" in your project. This means I can come back at anytime and fix/tweak.
  2. I can refresh the database schema imported and it will flag all the changes, and then the diagrams will automatically get updated.
  3. It generates really nice HTML interactive documentation. Major win! The major pro I found for this is with a little upfront work in arranging better, I could refresh all diagrams (maybe a new fk gets added), and then bulk export all these as HTML interactive database diagrams with full detail. You can hover over the FK lines and it will highlight the relationship etc. This is great for usability! Also, removes a lot of manual work in refreshing database column diagrams if something gets added/changed. One of the most significant points to this tool is the functionality + price! The price for a commercial licenses is extremely reasonable (as of 2015-12-09 the pricing was under $200) Some things that could use improvement:
  4. Improved autoarrangement - Allow preset option of only showing FK/PK ( i have an autohotkey script I built to do this, contact me if you want it, helped simplify)
  5. Allow optional layouts like Tree, Hierarchical

  • 9 min read

Best Tools for Taking Notes In Development

{{< admonition type="info" title="Updated: 2016-04-08" >}} Been working through some issues with Clarify app on my personal computer, something I messed up. @trevordevore with @clarifyapp has worked step by step through it with me providing some great personal assistance. In addition, he's given me some tips about my research in merging in a php script to automatically upload my images to cloudinary for hosting. I just finished producing some documentation with Clarify 2, and I have to say it's one of the best tools I can think of for producing a stellar documentation without requiring the user to spend time doing much formatting. I think the points in this post still remain, I wish they'd continue to expand it a little more. Maybe an extension that replicates a library of screenshots like Snagit, or something else for quick ad hoc annotation and pasting would be welcome. Overall, these are minor concerns with the great results the app already produces. {{< /admonition >}}

Developers have a lot to juggle.

Do a few of these things sound familiar:

  • Juggle current sprint tasks
  • Resolve outstanding issues needing immediate attention
  • Research/Professional Development
  • Long term development objectives that you can only get fragments of time to work on
  • Performance testing - and being able to remember numbers/figures with all the moving pieces
  • With all the time left blog, write an insightful blog post that will be hailed by millions

I've been on a long quest to evaluate the best tool to help me personally document and stay up to date with some of these tasks. Documenting and task management can be easily overlapped. For the purpose of my notes, I'm going to focus primarily on the documenting aspect. Whether you use Onenote, Trello, word, or any other system, the most important thing is an easy way to recap and evaluate all the work you've done

A few goals for a best fit

  1. Screenshots are a key for me (see next section if you wonder why)
  2. Annotation of screenshot images are a must
  3. Easily able to copy and paste to reuse the material or export in a way that others could consume
  4. Basic authoring, nothing fancy needed, just basic bold/italics, etc. Something to help visually organize, as raw text can be hard to skim through if a lot of content.

Why do care so much about screenshots?

The problem with solely using text for content is the variety of content we run across.

For instance, when I'm working on performance tuning a database, I will have text based details from SSMS, a grid of results that may be relevant. I may utilize a performance tuning tool like Qure Profiler, or find a diagnostic graph from perfmon that I want to preserve. The variety of formats is daunting to try and capture in a usable format into a documentation tool without considering screenshots. Since the other tools present information in a usable format, but not always exportable format I can use a screenshot to capture the data I reviewed without spending time trying to get things just right.

I also find visually walking through a problem easier to remember when re-reviewing in the future. Your mileage may vary.

Tools I Evaluated

Disclaimer: Please note that most of these tools I received a free license for indicating I'd review. I DO NOT base recommendations on these. A piece of crud is a piece of crud regardless of it it is free. I normally like to review products for an extended period before writing about any of them

My Final Review Rating after intense self examination, contemplation of my navel for hours on end, and the hope of lucrative contracts becoming a professional software reviewer....

Epic: Highly Recommended

  1. Clarify 2
  2. Snagit
  3. Camtasia Studio

Close: Good product

  1. Greenshot1. Great free screenshot utility. Best general screenshot tool besides Snagit I think I've found.
  2. OneNote
  3. Great for OCR scans of text, but basically useless for anything relating to annotations/commenting on an image.

Benched: Not a good fit for me *not reflective of all users

  1. Ashampoo Snap 8

    1.  UI was ok, but found it lacking with keyboard shortcuts and power user usage. The annotations were very dated and seemed very cartoonish for the most part, so I wasn't very happy with the end result. I think the app is a good one, just needs some redesign on a few elements for making it cleaner and modernized. The canvas expansion was very unintuitive as well, making it unfriendly for merging several screenshots together.

  2. Screenpresso

    1.  organizes by folder/project, which was a cool concept. Overall very limited and nothing that really stood out as a paid product.

  3. ShareX

    1.  Very extensible. One of my favorite features was the _on-screen annotations,_ which let you capture an area on the screen, and do basic annotations on it without any apparent GUI. Nice! Overall I can't recommend due to the learning curve and complexity. It's extremely powerful, but that comes at the cost of being very complex to configure and get working. Documentation was ok, but still for some more advanced setting up of custom share destinations I found it difficult to find help.

  4. ActivePresenter (free version)

    1.  Great capturing of screenshots and mouse movements into training video based on detecting movement/actions. This was a great find, however, I can't recommend it for any but those with no work budget, as it's extremely clunky for usage relating to development/notes/personal workflow. It's very busy and hard to find settings to configure, but then again... free!

All 3 of these products are commercial. All three have solid functionality, and I really find them to be beneficial to my development process. They each have a different scope in what they accomplish so I could see myself using each of them in various ways from this point forward.

Clarify 2

This was a gem I discovered and initially passed over. The site documentation focused a lot of documenting steps for tutorials/help, and I went ahead and tried to see what it would be like as it looked polished. So far, my favorite discovery in documenting/notes for development and blogging. This tool has the polish of a well design tool, while still having some powerful extensibility. I think it focuses very well on one thing which is documenting step by step work. This ties directly in with blogging and tech documentation. In a blog, I'm covering specific areas, and organizing my thoughts in blocks.

On the tech side, when I'm evaluating a SQL performance problem or tuning effort, I systematically am going through comparisons of before and after, impact assessment, and identifying the improvements against baseline. This tool allows this workflow perfectly.

The export options are pretty polished. I did a quick walk-through for a issue at work and got several complements about the polished documentation I produced. Best part is this took minimal effort.

Pros

  • Absolutely intuitive and clean
  • Lack of over complication with unneeded features. Instead, it focuses on usability.
  • The annotation style is my favorite, very modern and minimal, not like some that use more cartoonish annotation styles.
  • Rounding of images on canvas as a default, looks polished.
  • Easy manipulation of canvas, no complication in trying to add multiple images
  • Fantastic export options. Polished PDF, copy to clipboard as rich text, _EXPORT TO WORDPRESS _this article was written with this tool entirely* Cons
  • Keyboard shortcuts are lacking
  • Could really use with an extension of functionality to just capture single screenshots and annotate like snagit. As it stands, the tool is focused more on the documentation aspect, but with some enhancements it would work as a screenshot annotation tool. Sort of like a single step in their documentation tool, without all the rest.

Clarify 2 Image Annotation Editing

This image shows some of the useful annotation powers that clarify offers. The canvas that images are placed on can easily be re-sized with a mouse drag and images pasted.

Snagit

I avoided this product for a while as I honestly thought it was so mainstream that it wouldn't really benefit a power user/developer. So far, I'm glad to say I'm wrong. It's a well designed product that I've come to start using as my primary screen capture utility, along with Clarify

Pros

  • Wealth of export functionality
  • Good annotation capability with shapes, text, and so on.
  • Can capture screen video for a quick tutorial or walkthrough, no extensive editing options on this however. This is the focus of Camtasia.
  • Ability to tag screenshots (so bug screenshots could be easily re-reviewed)

Cons

  • Keyboard shortcuts are a little lacking with annotations
  • Some of the effects/annotations seems a little cheesy to me, but there are plenty of ways to find a good fit that formats in the style I like.
  • No rounded corner presets for the image canvas if you want that. There are workarounds, but nothing built in.

Snagit Extensibility with output

A wealth of export functionality is in Snagit, along with the additional plugins you can pull in.

Snagit Profile Extensibility with scripted profiles

Along with keyboard shortcuts and powerful export options, I found you could create capture profiles, letting you with setup a shortcut (keyboard or just menu driven) to capture and save as a special format, basically scripting several steps you'd manually repeat together.

Snagit Annotations

Annotations are pretty comprehensive with some functionality to splice out the middle of images (say a toolbar), merge multiple images, and more. Pretty well designed and along with Clarify 2 the best implementation from any utilities I've experimented with so far.

I did like the ability to "curve" an arrow on the annotations, as it let you smoothly draw an annotation that could curve around content without obfuscating it.

Camtasia

Camtasia crosses over the screenshot territory into more of a screen capture area. This functionality can be very powerful however, as I've found the ability to walk through tech issues with a recording is powerful. I'll probably blog with Camtasia eventually as I get more time to do audio recordings. The primary benefit I've found is the easy gif creation with full annotations, blurring, and other effects that help when providing examples while protecting some sensitive connection/context information.

With Camtasia, I've found it tremendously intuitive, as I've already worked with NLE (Non-linear editors), and the process of putting together a video with it is very simple. Some products I've tried that compare somewhat would be ActivePresenter, which is great for creating tutorials with a lot of "whitespace removed" by detecting the activity via keyboard and clicks.

However, I'd say it still has a hurdle of usability, and is not intuitive compared to the Techsmith offering, making it a great option if you have _zero _budget, but not a good option if you really want to create quickly a quality video.

conclusion

Quality tools can help the development workflow. I personally think a tremendous amount of value comes from utilizing images for varied capturing of work in addition to typed notes, as you can gain a lot of information this way. Hopefully, my suggestions might help spark a few new ideas for great tools to help with your workflow.

  • 1 min read

Dynamic SQL & Quotename

Not quite fineprint, but sure feels like it!

Quotename can be a pretty cool function to simplify your dynamic sql, as it can ease some of the escaping of strings. However, I normally use it for table/column names, and so hadn't ran into a "gotcha" of this function until today. It's limited to 128 characters, and if you pass in greater than 128 characters will yield a null. Yep... you could be trying to track down that error for a null string somewhere in your concatenation for a while... only to find out this silent error is occurring. I'd like to thank NoSqlSolution for mentioning this and helping me go back to the other window I had open and rereading it.... I guess sometimes it pays to read the darn BOL.

not-quite-fineprint-but-sure-feels-like-it-_w3xtwg

  • 1 min read

XML Attribute VS Element Assignment when working with SQL

XML handling with aliases

I find it interesting the difference in behavior with querying XML between column assignment, and quoted alias naming. It's a generally understood best practice to not use the deprecated syntax of column aliasing using a quoted name. For example:

select
    [escapedWithBracketsIsGood] = case when raining then cats else dogs end
    ,NoEscapeNeededPerGoodNamingPractices = case when rains then pours else friday end
    ,case when writtenThisWay then ICringe else UseAssignmentWay end as NormalWayMostFolksDoIt
    ,'BadNaming' = case when food then eat else hungry end
    ,case when work then eat else JobLess end as 'VeryBadNaming'
You can see the difference in naming. The bottom too are deprecated syntax from older naming standards, and should be avoided typically. Aliasing is pretty straight forward and the variance in doing your alias pattern doesn't normally have an actual impact on the results or way of handling... Except when doing with XML apparently

Image of XML Difference

Further reading

MSDN covers in more detail and precision in the this page The handling of XML is a newer area to me, as previously I've avoided like the plague. However, in working w/apps sometimes creating XML configuration files might be useful so exploring this can helpful to understand how to manipulate and even obtain values from it.

  • 2 min read

Split personality text editing in SSMS with Sublime Text 3

My preview post showed a demonstration of the multi-cursor editing power of Sublime Text 3 when speeding up your coding with SQL server.There is a pretty straight forward way to setup sublime (or one of your preferred text editors) to open the same file you are editing in SQL Management Studio without much hassle. I find this helpful when the type of editing might benefit from some of the fantastic functionality in Sublime.

External Tool Menu

Go to Tools > External Tools

external tool menu

Setup Sublime Commands to Open

Setting Value
Title Edit in Sublime
Command C:\Program Files\Sublime Text 3\sublime_text.exe
Arguments $(ItemPath):$(CurLine):$(CurCol)
Initial Directory $(ItemDir)

Limitation: Unsaved temporary files from SSMS are empty when you navigate to them. If you save the SQL file you will be able to correctly switch to the file in Sublime and edit in Sublime and SSMS together.

Important: One thing I personally experienced that wasn't consistent was the handling of unsaved files. If the file is SqlQuery as a temp file that hasn't been saved, then this opening didn't work for me. Once I had the file named/saved, it worked perfectly, even bringing the cursor position in Sublime to match what was currently in SSMS.

Refresh File3

Tools > Options > Environment > Documents You can setup the auto-refresh to be in the background if you wish, or manually select the refresh from SSMS when it detects the change. If the auto-refresh happens while you are editing sometimes it caused me to have redo some work (or control-z) in Sublime, but for the most part it's pretty seamless.

  • 1 min read

Multi-Cursor Editing SQL feels like the movie Inception just became real

  • Yes... multicursor editing is epic
  • No... SSMS doesn't support multi-cursor editing the way it should.
  • Yes... you can do some basic editing with multiple lines using alt-shift
  • No... it doesn't come close to what you can do with Sublime. Cool thing is you can open text in Sublime synced w/SSMS cursor position and switch between the two with a shortcut. That will be a post for another day, I'm just telling you now to get your appetite going. If you can't tell, I love shortcuts. Sublime + AHK pretty much covers most text editing needs you'll ever have. Feel free to send me a check for all the time you'll save from my revelation. I apologize in advance for the video quality. I plan on recording a better one in the future, after I wrap my head around the awesomeness of Camtasia Studio 8

Multi-cursor editing #2

  • 1 min read

Brentozar''s Training: Chocolate & Cowboy Hats Included

It was entertaining to listen to a technical wizard fighting the obsession with waiting to eat chocolate with 3k viewers watching. Kendra wore about 4-5 cowboy hats in an effort to help those of us who wear many hats feel welcome.... Now that's the kinda of training I enjoy! No pretense, just honest real, and insightful training with enjoyable humor included for free

Highly recommend attending the webex presentations occurring today and tomorrow with Brent Ozar and his amazing team. They are giving back to the community with some excellent training and Q&A. Attended the Shared Storage discussion yesterday and wasn't disappointed (I don't think they've ever disappointed with their training!) Register for the training now, as it is the second of the 3 days, with the first training occurring here @ 10am CST. Brentozar Event Registration

  • 4 min read

Monitoring SQL Server on a budget

Cheap ain't easy

There's a lot of tools out there, and very few that are polished, have a good UI, and some reasonable functionality to help monitoring, that don't cost an arm and a leg. One such tool I've recently begun to appreciate is MiniDBA . I was generously provided with a license to evaluate this and continue testing, and have recently had an actual chance to start using it more in my environment. The cost for MiniDBA is one of the most affordable I've found for a live monitoring tool with a good UI design (eye candy is critical for monitoring a server as we all know ) At the time of this post's original date, there is a simple free version for monitoring a single instance on the machine running. This free version is awesome if you have a VM running full-time, as you could have it stay running and monitor the instance you care about. Paying $50 for developer and $100 for Enterprise gives you more flexible management with alerts, multiple servers, and a service to collect the data instead of having to run the GUI app the whole time.

wait stats

Wait stats are the first place to typically go to when analyzing the delays a server may face. MiniDBA offers a few cool ways of looking at the data, including getting the diff on waits since the point in time you started looking at it, helping isolate the waits that really matter to you right now.

get alerts on critical server issues

I'd love to see this more extensible/customizable, but it's a good start. The time "resolved" would also be great when reviewing the history to be able to see how long before an issue was resolved.

general healthcheck on "best practices"

Again, some really cool stuff in here. I'd love more customization opportunity to actually expand or customize these as I have a boatload of custom DMV's for evaluating best practice setup conditions on a SQL server. It would be great to extend this more.

active connections

Pretty straightforward, but one plus is it offers ability to view the execution plan for each SPID, potentionally helping save a few steps. Note the execution plans are not shown at the server level "SQL tab", but at the database level. This reminds me of a less thorough "sp_whoIsActive".

other features

There are features to look at like: - table sizes - index sizes - files in the database - memory - default trace - last 3000 transaction log entries - locks on objects.

visual monitoring

The key of course for a great monitoring tool is not just a bunch of text data thrown at you, but a great visual representation of various facts so you can easily identify something wrong. I think the developer did a great job in providing a useful "dashboard". I think more customization or ability to look at a point in time more specifically would be great (like SqlSentry offers) but at the same time, the scope of the MiniDBA project seems to focus on simplicity, and not offering so much that it becomes complicated. I'd say for the price, the value is pretty good for a team looking for a simple tool with a few visual ways of looking at the performance, while still giving some active connection monitoring. Again, there's a lot of other options out there for monitoring, even the built in functionality. But for value, this is a pretty good option, as it seems to focus on simplicity, usability, and not being a $1000+ per server license.

  • 2 min read

Stranger Danger... The need for trust with constraints

I ran into an issue with errors with an database upgrade running into a violation of a foreign key constraint. Don't know how it happened. Figured that while I'm at it, I'd go ahead and evaluate every single check constraint in the database to see if I could identify any other violations, because they shouldn't be happening.

improve the execution plan by checking the data

In my reading, I found out that checking the constraints can enable the constraint to be marked as trusted. The trusted constraints are then able to be used to build a better query plan execution. I knew that constraints could help the execution, but didn't know that they could have a trusted or untrusted trait.

Brentozar to the rescue

I'm serious, this guy and his team are awesome. This one single team and their web resources have single handled helped me gain more understanding on SQL server than any other resource. I love how they give back to the community, and their communication always is full of humor and good examples. Kudos! Anyway, commendation aside, the explanation from sp_blitz was fantastic at summarizing the issue.

After this change, you may see improved query performance for tables with trusted keys and constraints. - Blitz Result: Foreign Keys or Check Constraints Not Trusted As the site further mentions, this can cause locks and performance issues, so this validation might be better done off hours. The benefit might be worth it though!

my adaption of the check constraint script

I appreciate the script as a starting point (see link above). I adapted to run this individually on each check constraint and log the errors that occurred. This runs though all FK and CHECK constraints in the database you are in, and then checks the data behind the constraint to ensure it is noted as trusted.