Counting the number of rows in a table

Originally posted on spaghettidba:

Don’t be fooled by the title of this post: while counting the number of rows in a table is a trivial task for you, it is not trivial at all for SQL Server.

Every time you run your COUNT(*) query, SQL Server has to scan an index or a heap to calculate that seemingly innocuous number and send it to your application. This means a lot of unnecessary reads and unnecessary blocking.

Jes Schultz Borland blogged about it some time ago and also Aaron Bertrand has a blog post on this subject. I will refrain from repeating here what they both said: go read their blogs to understand why COUNT(*) is a not a good tool for this task.

The alternative to COUNT(*) is reading the count from the table metadata, querying sys.partitions, something along these lines:

Many variations of this query include JOINs to sys.tables, sys.schemas or sys.indexes, which are not strictly necessary in…

View original 82 more words

QA for Data

Originally posted on Voice of the DBA:

Here’s a typical DBA scenario. You’re at your desk, working on something, when you get a trouble ticket, phone call, or tap on your shoulder. There’s a problem in production and an order, or some other important piece of data, is incorrect. Can you fix it?

Many of us are happy to oblige. Often we know what needs to be changed and can write the INSERT/UPDATE/DELETE statement to correct the issue. Even if we have to dig in further, we can usually find the data in a table and determine what should be changed.

However, what do we do then?

If you are asked the make data changes to production, do you QA the script? Do you save the scripts in a VCS or a log? Does anyone else examine the data or code? Do you scan the script yourself, have SQL Server parse it, or bother to look for…

View original 261 more words

Tracking Table Usage and Identifying Unused Objects

Originally posted on spaghettidba:

One of the things I hate the most about “old” databases is the fact that unused tables are kept forever, because nobody knows whether they’re used or not. Sometimes it’s really hard to tell. Some databases are accessed by a huge number of applications, reports, ETL tools and God knows what else. In these cases, deciding whether you should drop a table or not is a tough call.

Search your codebase

The easiest way to know if a table is used, is to search the codebase for occurences of the table name. However, finding the table name in the code does not mean it is used: there are code branches that in turn are not used. Modern languages and development tools can help you identify unused methods and objects, but it’s not always feasible or 100% reliable (binary dependencies, scripts, dynamic code are, off top of my head, some exceptions).
On the…

View original 1,405 more words

How do I get Java plugin working on Google Chrome? Re-Enabling Silverlight, Java and Facebook video!

Originally posted on .Net Diaries:

Today me and probably the 3 billions of people trying using java applet on chrome has found that after the java upgrade, chrome was unable to run java applet anymore, this not because of java, but because of google that decided to drop/stop the support for NPAPI, that is a cross-platform architecture standard used by many browser to enable plugins/applet such as Silverlight, Java and Facebook Video. You can find more directly from java website “How do I use Java with the Google Chrome browser?” But there’s also a good news, you can re-enable it, by doing these steps:

  1. In your URL bar, enter:
  2. Click the enable link for the Enable NPAPI configuration option.
  3. Click the relaunch button that now appears at the bottom of the configuration page.

…and here we go!! God bless the configurations!

View original

Unit testing with SQL Server article on SQLServerCentral.com

Unit testing with SQL Server article on SQLServerCentral.com

My “Unit testing with SQL Server” article is now online on SQLServerCentral.com. A big thanks goes to Steve Jones (@way0utwes).

art

You can read it here.

Stay tuned! :)