syscomments

Actually useful BI

Chris Miller — Wed, 18 May 2011 21:28:18 GMT

Kidding. Mostly.

Here's another BI-type analysis tool that's probably used by more people than any other BI tool in the world. It's cloud based, it provides incredibly detailed and useful information for performance analysis and identifying areas of improvement. Here's a sample report:

http://www.worldoflogs.com/reports/4iqif366qb2zrgpj/analyze/hd/source/?s=4429&e=4789

I think you should be able to see that without being logged in or anything. Let me know if you can't and I'll find a public report.

My point is, this is mainstream BI. This is actually useful business intelligence methodology being used to do real analysis by average people.

And if you're wondering...

Yes that's my guild
Yes I was the top healer :).

IsNumeric('.')

Chris Miller — Tue, 01 Mar 2011 23:08:13 GMT

So, anybody know why if you do this:

print IsNumeric('.')

it prints out this:

1

Seems that it won't convert to a float, it won't convert to an int, but it will throw lots of great errors.....

Quickie Post Describing Things I Want To Post

Chris Miller — Wed, 01 Dec 2010 17:12:13 GMT

First, I owe our proprietor here a review of Ignite Free or whatever from Confio. Here's the quick review: It's like liquid awesome poured into a coffee cup, ready to drink. Took 5 minutes to set up, 5 minutes to realize "holy crap I need to get this pointed at production NOW!". 5 minutes to ascertain the server load caused by monitoring (almost none). 1 minute to get it pointed to production, and then 5 minutes to start finding and fixing horrible queries. The biggest part of a performance tuning task is finding out what's slow. This tool DOES THAT FOR YOU. In a way that's complete, inobtrusive, fast, accurate, and amazing. If you're not using this tool, you're a tool.

Second, I want do a nice writeup of Change Data Capture, which looks just unbelievably good. This one feature is good enough to justify an Enterprise Edition license. Seriously. It's that good. I'm gonna get this project done and then do a review for y'all.

MERGE, the degenerate case

Chris Miller — Wed, 01 Dec 2010 17:03:31 GMT

Looking on teh interwebs for some example of how to write a very simple MERGE statement. I haven't done much with MERGE. I want to update one column, one row, from a variable. If the row exists, update, if the row doesn't exist, insert it. This is, in my mind, the bread-and-butter MERGE. What MERGE was written to do. Yeah, it'll do a ton more than that, but the very simplest case, the degenerate case, would be something stupidly simple. There are a bunch of really simple people on teh interwebs, I'll ask Mr Google how to do this. I'm sure someone's covered it...

Every single example I found on the internet was a regurgitation of the one in Books Online. Every Last One. Blog posts by renown MVP's, social.msdn posts from people who want to be MVP's. Basically cribbing BOL for content and changing some column/table names around. It was funny and sad at the same time. Wow, really, you can do a join in a MERGE? Fascinating. But that's something I'll do once in a blue moon, why did you pick that example Mr Blog Post Author? Oh, because it's the one in BOL and was the least amount of work. Sigh. Nobody really explained what every line of MERGE does. Because that would be useful and involve work and wouldn't be easy to search engine optimize, so wouldn't provide revenue.

Since the proprietor here pays me a flat rate in beer, I don't care about revenue. So here ya go, actual useful content.

declare @From_LSN_Leadraw binary(10)

SET @From_LSN_Leadraw =  sys.fn_cdc_get_min_lsn('dbo_leadraw')

        merge into CDCTransactionTracking

        Using (select @From_LSN_Leadraw From_LSN_Leadraw) Source

        on CDCTransactionTracking.Tablename = 'LeadRaw'

        when matched then update set MinLSN = Source.From_LSN_LeadRaw

        when not matched then insert (TableName, MinLSN) values ('LeadRaw', Source.From_LSN_LeadRaw);

So let's break this down.

First, specify the target table, in this case CDCTransactionTracking. I could alias this to a name like Target, but this is an engineering POC, not pretty production code. The next line specifies the data source. You can't source from a scalar variable, so this does a select and creates a little quickie rowset that is enough to fool the compiler and let it run.

Yes you could use a table variable here. Every time you create a table variable, god kills a kitten. I don't particularly like cats, but that's just unnecessarily cruel.

Next, the ON clause. We need to define what's going to join the target and the source. In this case, that's a constant.

After that, what should happen when the statements match. When that happens I just want to do an update. Easy peasy. That line could also read set MinLSN = @From_LSN_Leadraw. Same thing.

Then when we don't match, we want to do an insert. Pretty straightforward. Again, could do @From_LSN_Leadraw.

There ya go. Original work. On the internet. No kittens harmed or copy/paste/Search/Replace from BOL.

Max Server Memory....OOPS

Chris Miller — Mon, 22 Nov 2010 20:00:19 GMT

Max Server Memory is actually Max Buffer Pool Memory.
From the knowledge base:

Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration.

That explains why it's using 30 and change GB when I set it to 28GB. Oops. Fortunately, my sysadmin agrees with me that any resource that isn't maxed is wasted.

The optimist says the glass is half full.

The pessimist says the glass is half empty.

The engineer says the glass is twice as big as it needs to be.

Tables, and the space they use

Chris Miller — Tue, 05 Oct 2010 22:21:21 GMT

So I hacked around in sp_spaceused and got this sorted out. Works in SQL 2005, haven't tested north or south of that.

Share and enjoy.

drop table #mainpartitionstats

drop table #LOBPartitionStats

drop table #totals


    SELECT

        object_name(object_id) name,

        SUM (reserved_page_count) reserved_page_count,

        SUM (used_page_count) used_page_count,

        SUM (

            CASE

                WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

                ELSE lob_used_page_count + row_overflow_used_page_count

END

            ) Pages,

        SUM (

            CASE

                WHEN (index_id < 2) THEN row_count

                ELSE 0

END

            ) RC

    into #MainPartitionStats

    FROM sys.dm_db_partition_stats

    group by object_name(object_id)

    SELECT object_name(p.object_id) name,

        sum(reserved_page_count) Reserved_page_count,

        sum(used_page_count) Used_page_Count

    into #LOBPartitionStats

    FROM sys.dm_db_partition_stats p

        inner join sys.internal_tables it

            on p.object_id = it.object_id

    WHERE it.internal_type IN (202,204)

    group by object_name(p.object_id)

    select name, sum(reserved_page_count) reserved_page_Count, sum(used_page_count) used_page_Count,

        sum(Pages) pages, sum(rc) rc

    into #totals

    from (

        select name, reserved_page_count, used_page_count, pages, rc

        from #mainpartitionstats

        union

        select name, reserved_page_count, used_page_count, 0 pages, 0 rc

        from #LOBpartitionstats

) c

    group by name

    SELECT name, rc as [rows],

        reserved_page_count * 8 Reserved_Space_KB,

        pages * 8 Data_Space_KB,

        case when used_page_count > pages then (used_page_count - pages) * 8 else 0 end as index_space_KB,

        case when reserved_page_count > used_page_count then (reserved_page_count - used_page_count) * 8 end as unused_space_kb

    from #totals

    order by 3 desc

COM+, DTC, and 80070422

Chris Miller — Thu, 11 Feb 2010 17:18:42 GMT

One of our "packaged" software bits that accesses my servers is going through an upgrade right now. Apparently this software requires DTC to be installed on my SQL Server, and able to accept remote connections. So I look up how to do that in the knowledge base:

http://support.microsoft.com/?kbid=555017

And immediately hit a roadblock. The DTC components aren't showing up in my Component Services console. The entire console's acting weird (well, weirder than usual) and when I go into the console and click "Options" it insists on having a timeout entered, and when I enter one, close the box, and go back, the setting's gone again and I'm required to re-enter it. Lots of weirdness, and no DTC tab. If you open the COM+ folders, you immediately get error 80070422.

After a lot of searching I was looking through the Services listing on the box (after restarting DTC for the twelfth time) and saw that "Com+ System Application" was disabled. I set it to manual, rebooted the box (test server) and everything started working.

So, if you're trying to follow those instructions and discover that the Component Services tool is acting odder than usual, make sure that service isn't disabled.

Star Trek Online Open Beta, Thumbnail Review

Chris Miller — Tue, 19 Jan 2010 02:13:41 GMT

I figure there's got to be some crossover between the SQL Server folks and the MMO folks, so here ya go.

I downloaded and played the new Star Trek: Online open beta this week, just to see if it was going to be worthwhile. So here's some of my thoughts on it.

It's an MMO, Massively Multiplayer Online game. Typically that means you'll buy a box at your local retail outlet for $49.99 and then pay a monthly subscription fee, probably $15.

It's also a Role Playing Game. I was looking forward to it, thinking it'd be a really interesting social experiment to get people to work together to run a ship.

Wrong.

So it's an MMO, and you're basically a one-man army, you have a ship of your very own, that you can fly using your mouse. As long as you don't want to go upside down. It's the Federation, son, YOU WILL FLY UPRIGHT AT ALL TIMES. The weapons and upgrades are the predictable "more power" upgrades you'd get at the beginning of any MMO. There seemed to be a lot more MMO jargon used in the weapon descriptions (weapons have DPS, armor/shield penetration, etc) that might be opaque to people who haven't done a lot with MMO's before. That is likely to change, this is a beta.

Basically there are 2 "modes" to the game. Ship mode, where you're flying your ship, and ground mode, where you're running around hallways as a person.

The ship combat was...well, boring. Fly around (but not upside down) and shoot things. make sure your weakest shield is facing away from your enemy. Hold down the space bar to auto-fire. It takes about 2 minutes to master. The shield thing is interesting too, if by "interesting" you mean "really simple and cheesed-out". There are 4 quadrants to your shield: fore/aft/starboard/port. If you fly above or below someone, it's pretty much *random* what's going to get hit. Your energy weapons have a 200 degree firing arc, facing either forward or aft, but ignoring up and down. Last time I checked, space combat was supposed to be 3D. So it's a 2-d based combat where you can fly in 3 dimensions, sort of.

You can customize your ship pretty much on the fly. Whenever you're out of combat, you can completely replace your weapons with any that you salvage (loot) off another ship you kill. You know, what would take a drydock and several weeks takes 2 seconds in MMO-land.

Then occasionally you'll touch down on the planet and be the away team. Now this is Borg-era Star Trek, and they let you, the captain, the person responsible for the ship, beam down into dangerous situations with the away team. Hrm. Yeah, so that regulation obviously didn't make it into the RPG. When you're on the ground (or in another ship) the game acts like the world's most dumbed-down third person shooter. Click your target, click your attack. Repeat until target dies. You have 3 attacks (2 shooting and a melee knockback) and that's...well, that's it. You have medical packs too. I never really felt in enough mortal peril to use one though.

One of the things I like about MMO's is specialization. I can pick a character class, learn that character class completely, and then I'll be a fairly good player. ST:O went a different way. You choose a character class, but I'm not exactly sure why, since no matter what class you pick you get the same skill trees, leveling track, and role. You're the captain. That's it. Except when you're not the captain, and you're having to learn the roles of all the other classes so you can populate your bridge with good officers. And then know all the other skill trees, so you can level those officers as they gain experience.

It's like they took the worst parts of most RPG's (having to micro-manage a team of NPC's), the worst space flight game ever, and a really bad 3rd person shooter and crammed them into one package.

So far: Bad character management, bad space flight, and bad third person shooter. So how's the social part?

Um. Yeah. There's a chat box, and it scrolls the Star Trek equivalent of Barrens chat. People asking where different things are. Someone actually (jokingly) asked where Mankrik's Wife was (a WoW joke) just to make the scene complete. You *can* team up and there's a system called "Fleets" which look like a guild based system. I didn't stick around long enough, the game mechanics were, frankly, boring me to tears.

The graphics are...fine. I guess. I have a midrange video card, there was no video lag (lots of server lag though). Everything looked pretty enough, which for an MMO means "about 2 generations behind any other rendering system". Phasers look like phasers, photon torpedos look like photon torpedos. Nothing earth-shatteringly pretty or mind-numbingly awful.

There are lots of loading screens. In the WoW universe you can travel from the far southern end of the world to nearly the northern tip of the world with no loading screens and smooth transitions. If you attempted to walk that distance it would take probably a couple of hours, which is why there are lots of fast-travel options in WoW. There were loading screens inside ST:O within the same building. Small building. An area that took 30-45 seconds to run across would have a loading screen to get to it.

There were lots of glitches, which I mainly account to server lag. I'm not sure that a shaky launch is going to do these guys any favors though when it comes to winning enough market share to be viable. The only thing that Age of Conan did right was get have stable servers at launch and through beta. ST:O's operations people really don't understand how to seed torrents, it took me about 2 days to download the client software through their system, and the torrent wasn't seeded at all before it went live. You know it's going to be fun when uTorrent says under "estimated time" 3y3d.

There were running glitches (run along, suddenly you rewind to 50 yards back down the hall), ship transition glitches (you beam up and you see your character running in space). Ship flight glitches (you'd rewind to about 50...uh...probably meters back sometimes). I'm going to attribute most of the glitchiness to the beta quality server software. That's fine, that's why you have betas. The gameplay mechanics, however, were just awful, and that's something you do in design, not in beta.

A pretty horrible game so far. I hope it gets better, but I'm not going to be buying it.

SQL Server 2005 SP3 install issue

Chris Miller — Tue, 29 Dec 2009 14:55:38 GMT

So I got this message:

MSP Error: 28017 SQL Server Setup cannot write to removable media. To continue, specify a directory on a hard disk attached to the computer where SQL Server will be installed.

And I'm installing to locally attached storage. After about 20 minutes of cursing, rebooting, and cursing some more, I went to My Computer and disconnected all my network drives. My logon script helpfully maps network drives, I'd forgotten about them since mapping drives sucks and I don't ever use them (UNC paths for life!). So I unmapped them and suddenly it worked.

Stupid bug, bad error message, and stupid logon script.

Installing SQL Server 2005 on a Windows 2008 R2 Cluster

Chris Miller — Mon, 28 Dec 2009 20:15:35 GMT

I just had an interesting issue with an installation I thought I'd share. I was installing SQL Server 2005 on a Windows Server 2008 R2 cluster. The cluster validates properly (use the cluster validation tool in 2008 R2, it will save you problems) and failed over properly prior to installation. When installing SQL Server 2005, I got through putting in all the information and when it came time to actaully install the software got a "User has cancelled the installation" error, and a notification to check the logs. The logs said:

Waiting for actions from remote setup(s)
Breaking wait state and aborting package due to cancel code received: 1602
Remote setup(s) are ready
Notify package action is determined: 1602
Error Code: 0x800700e9 (233)
Windows Error Text: No process is on the other end of the pipe.

Source File Name: remotemessagelib\overlappedpipelistener.cpp
Compiler Timestamp: Fri Jul 29 01:13:51 2005
Function Name: sqls::OverlappedPipeListener::writePipe
Source Line Number: 294

The installation was failing when it attempted to attach to the other cluster node. The resolution is:

1. Make sure the task scheduler service is running on the remote cluster node. If the task scheduler service is a cluster resource, fail that resource over to the remote node (the latter was not an issue on my side, I've read questions from people asking about it though).

2. Go into your registry (insert standard "don't ever edit the registry" disclaimer here) and go to HKLM/System/CurrentControlSet/Control/LSA and change the "disabledomaincreds" from 1 to 0 on EACH CLUSTER NODE and then reboot BOTH CLUSTER NODES.

Then the install worked like a champ.

Big thanks to MS Sql PSS for getting this fixed quickly.

Left-duplicate indexes

Chris Miller — Thu, 12 Nov 2009 17:05:41 GMT

At Kim Tripp's PASS session on indexing and covered indexes, she more-or-less proved that wider is better for indexes, within reason and searchability. It's better to have the index (Column1, Column2, Column3) than the index (Column1) and then a (Column1, Column2) and then a (Column1, Column2, Column3). And the first two of that set are redundant and don't provide any significant performance improvement over a wider index in most applications.

Finding cases where you have multiple indexes like that isn't a trivial problem. Here's the answer I came up with. This particular query finds cases of a (Column1, Column2....ColumnN) with a (Column1) but it could be pretty easily modified to do a multiple left side key required. It's all filtering, just add [3] and the appropriate group by/max and you're set. And once again, yes, you could do this without a temp table, but the temp table's kind of interesting on its own.

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

select object_name(object_id) oname,

index_id, key_ordinal, Column_ID

from sys.index_columns) AS SourceTable

pivot(max(column_id)

for key_ordinal in([1], [2], [3], [4], [5],

[6], [7], [8], [9], [10], [11], [12],

[13], [14], [15], [16], [17], [18], [19])

) as PivotTable

select oname, [1], max([2]), count(*)

from #TableList

group by oname, [1]

having count(*) > 1

order by 1

Finding tables with no unique indexes

Chris Miller — Wed, 11 Nov 2009 20:30:13 GMT

Same vendor, different issue. How many tables in the database aren't really tables, but are actually just 2 dimensional data stores of unknown quality? Tables should have a unique index. Of some kind. If it's a formal PK, or a unique constraint or a unique index, it needs have *something* unique enforced, or it's not really a relational table, but is instead a pile of unknown quality data.

select so.name

from sys.objects so

left join sys.indexes si

on si.object_id = so.object_id

and si.is_unique = 1

where si.index_id is null

and so.type_desc = 'user_table'

order by 1

428 tables with no unique indexes. This goes with the 36 completely duplicated indexes and 50+ partially duplicated indexes.

And by the way, I checked. is_primary_key implies is_unique, and is_unique_constraint implies is_unique.

Finding duplicated indexes

Chris Miller — Wed, 11 Nov 2009 15:45:40 GMT

So I wanted to start being a bit more aggressive about index management. Specifically, I want to be able to find duplicated indexes inside of one of my vendor databases. So (1) I can make fun of them even more than I do already, and (2) so I can give them a script of all their issues that they might be able to resolve them.

Here’s what I came up with. Only really works if you have 19 or fewer columns per index. Why 19? I got tired of typing there. You can write this up as a code generator if you don’t like it. There are better ways to do this, but the way I’m doing it you can look at the temp table and use it to find the more subtle problem of a table with two indexes which are subsets of each other, which are usually but not always redundant. Anyway, here ya go.

drop table #tablelist

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

select object_name(object_id) oname, index_id,

key_ordinal, Column_ID from sys.index_columns) AS SourceTable

pivot (max(key_ordinal)

for column_id in ([1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

)

) as PivotTable

select oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13],

[14], [15], [16], [17], [18], [19]

from #TableList

group by oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

having count(*) > 1

order by 1

How to specify a Windows Authentication user in T-SQL

Chris Miller — Tue, 18 Aug 2009 16:26:10 GMT

This may only be a SQL Server 2005 problem. Comments either way please.

Only one of these works:
grant execute on stored_proc to [domain1\ismom]
grant execute on stored_proc to 'domain1\ismom'
grant execute on stored_proc to '[domain1\ismom]'

Only one of these Execute As User works:

execute as user = 'domain1\ismom'
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert

execute as user = '[domain1\ismom]'
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert

execute as user = [domain1\ismom]
exec master..xp_sqlagent_enum_jobs 0, 'sys-admin'
revert

The execute as user doesn't give you a syntax error, by the way. Instead it goes all cryptic:
Msg 15404, Level 16, State 11, Line 1
Could not obtain information about Windows NT group/user '[domain1\ismom]', error code 0x534.

Some consistency here would be nice. Usable error messages are a plus also.

For grant execute, use the bare brackets [domain1\ismom]
For Execute As User, use the quotes without brackets: 'domain1\ismom'

Someone might not understand how CASE works....

Chris Miller — Tue, 05 May 2009 14:35:14 GMT

case when StatusFlag = 'LWCA' and TransactionType <> 'W' then 'Layw Cancel'
        ELSE
    case when StatusFlag = 'LWCA' and TransactionType = 'W' then 'Warranty Cancel'
        ELSE
    case when StatusFlag = 'WAPK' then 'Warranty Pickup'
        ELSE
    case when StatusFlag = 'LWSE' and TransactionType <> 'W' then 'Layaway'
        ELSE
    case when StatusFlag = 'LWSE' and TransactionType = 'W' then 'Warranty Setup'
        ELSE
    case when StatusFlag = 'SALE' and isnull(HV.QTY,0) > 0 and layawaynumber is null then 'Sale'
        ELSE
    case when StatusFlag = 'SALE' and isnull(HV.QTY,0) > 0 and NOT layawaynumber is null then 'Lwy Pickup'
        ELSE
    case when StatusFlag = 'SALE' and isnull(HV.QTY,0) <= 0 then 'Return'
    End End End End End End End End,