Voice of the DBA

Tag: T-SQL

  • Hints Are Not Always Better

    Is this better than an index scan?

    I have always thought that an index seen was preferable to an index scan. It seems like the general rule that so many DBAs and developers follow, looking to convert every scan in an execution plan to a seek. Often that results in better performance, and I’ve seen many people resort to using hints to enforce this behavior in SQL Server when the query optimizer (QO) or Query Processor (QP) fails to choose their indexes.

    This past week Rob Farley wrote a great blog post that taught me something about seeks, scans, and the fact that one is not always better than the other. It has a great title and is worth a few minutes of your time to read: Covering, schmuvvering – when a covering index is actually rubbish. In the post Rob shows that a seek can be worse than a scan in some cases, in his example due to a Residual Predicate.

    I have seen so many people mistrust the query processor in SQL Server over the years, often resorting to hints when it seemed that the best index wasn’t being chosen. I’ve felt like doing that before as well, spending afternoons cursing the developers at Microsoft that their product wouldn’t choose an index that I knew was a better choice.

    Over the years I’ve talked with the people that build the code behind the query optimizer and often it seems someone is submitting a bug in the way the QO/QP works. Most of the time, however, I find my respect growing for that team, and often find that the individual is falling victim to the “it works on my machine” syndrome. Too often someone is observing a single case, a single data set, and limited concurrency, all of which can drastically change the performance of a query on your system when they grow.

    SQL Server doesn’t have a perfect QP/QO system, but it has a very, very good one. Using too many hints almost feels like hard-coding a value in the system. There are times that it makes sense, but they are very rare.

    This post also reminds me that there are so many things to learn about SQL Server, and gaining a deeper understanding of how the internals of SQL Server work can pay off with much more efficient, and scalable code that handles your load as it grows.

    Steve Jones

    PS – This post makes me want to see Rob’s pre-conference session this October at the PASS Summit. Hopefully he will get picked and many of us will get the chance to learn more nuggets like this one.

  • Collation Conflicts in a SQL Server Join

    I went to run this query recently: 

    select TOP 10 * 
 from users a
   inner join Banned b
   on a.username = b.username

    and got this lovely message.

    collation

    I’d seen that message before, so I knew what was wrong. The collations for the two tables were inconsistent. Since this was a database that was upgraded from another version of SQL, and uses objects from a third party, I wasn’t surprised that a specific collation was used. I had created the “b” table myself, using database defaults, and they didn’t match the object.

    I did a quick search since I couldn’t remember the exact syntax for the clause to add to my query. I ended up at a friend’s blog, Pinal Dave’s SQL Authority, and read this post: Cannot resolve collation conflict for equal to operation.

    The fix is easy, add a COLLATE DATABASE_DEFAULT to the join condition to force a specific collation on the field. I could easily have added a COLLATE Latin1_General_CI_AS as well, but since I knew that the second field was database defaults, I did this:

    select TOP 10 * 
 from users a
   inner join Banned b
   on a.username COLLATE DATABASE_DEFAULT = b.username

    Worked fine, and I was on my way.

  • Checking Permissions

    Someone posted this query recently:

    select a.*,name, b.* from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT' and b.grantee_principal_id = a.principal_id

    That’s a little ugly, so let’s fix it:

    SELECT  a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
 FROM sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE permission_name = 'INSERT'

    If you run this, you’ll get INSERT permissions in your database. In this case, the person had one row returned that had “public” in it, as shown.

    results1

    I normally don’t have permissions for public, but in this case I had run this first:

    GRANT INSERT ON Person.Address TO Public

    I don’t recommend permissions for public, and you really ought to run this on all your servers:

    SELECT a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
 FROM sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE a.name = 'public' AND major_id > 0

    How do you find out which objects have permissions? There’s a clue in the last query. If you scroll across in the results, there’s a major_id column. You can use that to find the object.

    results2

    The OBJECT_NAME function is handy here, and it takes an object_id, which is the major_id. If I run this:

    SELECT OBJECT_NAME(85575343)

    I get “Address” back, which is the object I altered.

    And, of course, we need to clean up

    REVOKE INSERT ON Person.Address TO Public

  • Clean Code is Easier to Read – SQL Prompt

    I saw a post recently that had query that looked like this:

    select a.*,name, b.*
 from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

     

    Ugly to read, at least to me, and in a poorly written format. The table, table format isn’t ANSI compliant and isn’t recommended. So I did this:

    formatsql

    A little better, and easier to read, but not great.

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a ,
        sys.database_permissions b
WHERE   permission_name = 'INSERT'
        AND b.grantee_principal_id = a.principal_id

    However now I can make a few quick edits. Remove the comma between tables and add “INNER JOIN” and then move the AND clause up to an ON clause to give me this:

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
    ON b.grantee_principal_id = a.principal_id
WHERE   permission_name = 'INSERT'

    Much better, and easier to read.