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:
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...
So, anybody know why if you do this:
it prints out this:
Seems that it won't convert to a float, it won't convert to an int, but it will throw lots of great errors.....
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...
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...
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...
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
SUM (reserved_page_count) reserved_page_count,
SUM (used_page_count) used_page_count,
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
WHEN (index_id < 2) THEN row_count
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:
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...
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...
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...
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...