Powershell vs. TSQL
Dan Jones, from the SQL Server manageability team, posted on “PowerShell vs. T-SQL or Why Did We Add PowerShell Support in SQL2K8″ and then Jeffery Snover from the powershell team expanded on this topic. Lots of good info and strong words. To quote, “If you work with Microsoft Server products, you are going to be a PowerShell user.”
It has been a a long day so I will not add commentary right now although there were a few WTF moments while reading. With that, I will leave you with 21 Characters.
SQL Server Videos
I ran across SQL Server videos on .netkicks (RSS). I think this is a fairly new site and I must say it is pretty nice. Good clean layout with RSS feeds to let you know when new stuff arrives. You can stream with WMP or silverlight AND you can download in 3 different resolutions plus ipod format. On top of all of that, there is a transcript of the video.
I watched the 400 level video on extra wide covering indexes. It was well thought out and I learned something new. What more could you want? Oh yah, it is free!
Update: You can also follow Michael on twitter here.
Captain Varchar(MAX) and the Pagelatch Posse 7
This is my humble follow up to the, now classic, xkcd strip on little Bobby Tables.
Reporting on your OLTP system
Just a quick note on this topic since it really is not a best practice and I am sure no one does it. 
Business requirements > best practices?
Even if you have a reporting server dedicated for …err… reports, the business might dictate real time data in those reports. That may or may no be possible on a reporting server but that is another post. Most of the time, the reporting server is not real time. At the same time, the OLTP data is not optimized reporting and if it is, transactional performance decreases. The solution usually dictates some reporting functionality being added to your production OLTP server. Maybe not at a DSS level but an adhoc query built by a web page. A company’s order history, an store inventory, or even an advanced search web page. You know the one:
select * from widgets
where color is null and size is null and flavor is null…….. and (title like ‘%ice cream%’ or description like ‘%ice cream%’ or keywords like ‘%ice cream%’)
Is that really a reporting database?
So you have a copy of your OLTP database either through mirroringsnapshots, logshipping or replication that you call a reporting database. What does that buy you? Well, we you are offloading reporting right. Kinda. You separate your reporting queries and your OLTP queries but here are some downsides:
- The reporting database is not optimized for reporting. This tends to be really apparent if reporting is critical.
- The OLTP database now has read activity on the log(except for logshipping)
- The reporting database still takes all of the write traffic in one way or another.
- The OLTP database may lose resources. For example, less disks get dedicated to it. Or worse, reporting and OLTP end up on the same physical SAN disk basically doubling the IO.
So what is the solution?
The real solution is to design an incremental ETL process that loads to a report optimized database. If a closer to real time data is required, triggers, modified replication or possibly asynchronous triggers might be the way to go. I am not going to pretend to know how to architect a solution like that. However, it is safe to say that a nightly or real time solution will require some serious dev work. This includes creating the ETL process and rewriting the application to use the report optimized database.
Paint yourself into a corner?
Say you have a home grown database that has gone from megabytes to 100’s of gigabytes. Separating reporting functions to a new database is going to take time. However, SQL Server 2008 provides new features that scream consolidation. Not only instance consolidation but functionality consolidation.
- Resource Governor is the backbone of a consolidation strategy.
- Filtered indexes – Index the different workloads while impacting writes as little as possible.
- Data Compression - combine with partitioning and shrink the reporting data while leaving the really hot data uncompressed.
- Not just SQL 2008 but don’t forget about indexed views and indexed persisted computed columns.
The other assumption of consolidation is larger hardware. If you are not spending money on a copy of OLTP reporting server, you can get a larger OLTP box.
Warning: this is forward looking since I don’t have any production SQL Server 2008 servers yet. 
Conclusion
Not to sound like a Microsoft fan boy but SQL Server 2008 Enterprise Edition provides a lot of benefits for mixed workload boxes. However, the real point of the post is that making a copy of the OLTP database for reporting may not provide gains one would expect.
71-451: Microsoft SQL Server 2008, Designing and Optimizing Database Solutions
Yet another beta exam announcement. This one is for the developer track, MCITP Database Developer 2008. It is actually the second and usually harder test. I guess they just got finished with it before 71-433. I have taken 3 of the beta tests so far. I have reviewed 71-431 and 71-450. I have yet to get results. As I mentioned before, I suggest you taken them and provide feedback. This will improve the test and thus the certification. Registration opens 8-29 and you can get the reg code here.
p.s. It is free!
Indexed Views vs. Filtered Indexes Part 2
In the first part, we looked at the a very simple example with a single table SELECT using covering indexes. This one is a little more complex. We are still covering but we are joining two tables, ordering and grouping. Filtered indexes do not support joins so we have to create two indexes. In this test, the indexed view wins but filtered indexes come a respectable 2nd place compared to normal indexes.
The DDL(2.5M in tblitem and 25M in tblitemdetails)
CREATE TABLE [dbo].[tblItem](
[Itemid] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](100) NULL,
[Datecreated] [datetime] NULL,
[Closed] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Itemid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblItemDetails](
[ItemDetailsID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [int] NULL,
[Color] [varchar](20) NULL,
[Size] [varchar](20) NULL,
[Flavor] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[ItemDetailsID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblItemDetails] WITH CHECK ADD CONSTRAINT [fk01] FOREIGN KEY([ItemID])
REFERENCES [dbo].[tblItem] ([Itemid])
GO
ALTER TABLE [dbo].[tblItemDetails] CHECK CONSTRAINT [fk01]
GO
--Create normal and filtered indexes
create index ix01 on tblitem(Datecreated, ItemName)
create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22' and itemid > 0 and itemid < 50
create index ix01 on tblitemdetails(itemid, color)
create index ix02 on tblitemdetails(itemid, color) where itemid > 0 and itemid < 50
--Create indexed views
alter view ivw01
with schemabinding
as
select a.ItemID, b.Color, COUNT_big(*) as cnt
from dbo.tblItem a join dbo.tblItemDetails b on a.Itemid= b.ItemID
where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50
group by a.ItemID, b.Color
create unique clustered index ix01 on ivw01(itemid)
create index ix02 on ivw01(itemid, color)
Test Queries
set statistics io on
--Using a normal covering index
select a.ItemID, b.Color, COUNT_big(*) as cnt
from dbo.tblItem a with (index=ix01) join dbo.tblItemDetails b with (index=ix01) on a.Itemid= b.ItemID
where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50
group by a.ItemID, b.Color
order by cnt
--using a filtered index
select a.ItemID, b.Color, COUNT_big(*) as cnt
from dbo.tblItem a with (index=ix02) join dbo.tblItemDetails b with (index=ix02) on a.Itemid= b.ItemID
where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50
group by a.ItemID, b.Color
order by cnt
--using an indexed view
select * from ivw01
order by cnt
IO numbers:
Normal covering indexes
(8 row(s) affected)
Table 'tblItemDetails'. Scan count 8, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Filtered covering indexes
(8 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 99, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblItemDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Indexed View
(8 row(s) affected)
Table 'ivw01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Note: By increasing the result set 100 fold(itemid > 0 and itemid < 5000), we see very similar results except with merge joins.
Query Plans:
The query plans are different. Most notably being the scans for the filtered index and indexed view vs. the normal index. This is fine though. They as just a fraction of the size of the normal index.
Conclusion
The mere fact that the indexed view runs with 50 times less IO overhead does not make it the automatic choice. What if you are on standard edition? Filtered indexes work on standard edition. Also we filtered on a date column. What if you need a real time rolling total? You have to use a normal index. Know you tools and then test, test and test some more.
71-450 Beta Test Review
I wrote this test today. It was not bad. Some questions were good things that DBA’s with real life experience should know. Some were really easy. Some were like WTF? I read this before taking the test and it is pretty right on as far as the content. One think that surprised me were the “administering SSIS packages” questions but that was listed in the prep guide. I just missed it.
I did make a lot of comments. There were a few questions that could not have a correct or at least totally correct answer. There were a few questions that required more info to make more than a guess or an answer based on opinion. I also commented with harsh words on questions that could be easily answered by reading the marketing blurbs on 2008. I actually ran out of time making comments.
If you have been working on SQL 2008, go take this test, and provide feedback. They are listening, and hopefully, the end result will be more street cred for MCITP.
Get the beta codes here: http://blogs.technet.com/betaexams/default.aspx
CodePlex Project: Qpee Tools 1.0
This is going to be my submission for the SQL Heroes contest. Have you entered? I don’t really care about winning but it would nice if it kick started a community generating useful SQL tools. I may get in a few point revisions in before September especially if I get some good feedback.
I was totally going to create a set of SSRS reports that combine the historic data from the data collectors with real time OS info from WMI. However, the GUIManageability team did such a great job that my UI skills pale in comparison. I changed directions at the last minute after RTM.
Please check it out and let me know what you think. I have been staring at this project for the past week+ so my perspective may be off. Let me know if something doesn’t make sense. Here is the description:
QPee Tools 1.0
QPee Tools is set of tools to log SQL Server 2008 query plans, identify plan regressions and performance problems because of plan regressions. It utilizes the query_hash and query_plan_hash features introduced in SQL Server 2008 along with the execution dynamic management views and functions.
QPee is built around a logger stored procedure and job. The logged query plans allow for comparisons with what is in the procedure cache andor currently executing. Two tools are provided to assist with this analysis. See usage.sql for more information on these tools.usp_Now – This is like Activity Monitor on steroids. Not only does it show what is currently executing, the current query plan, the previous query plan(if any) and various stats but it goes beyond just reporting on cumulative totals for CPU and IO. Cumulative totals do not provide a lot of value if you need to see what is currently pegging the system.
usp_CheckCache – You can run this against what is currently in the procedure cache to see if any queries are using new plans. For example, you could use this after applying a service pack or hotfix to see if there were any negative optimizer changes.
If either of these tools show that you are currently using a suboptimal plan, you can use the logged xml plan in a plan guide.usp_PlanLogger_SchemaChange – There are valid reasons for plans to change like adding an index. This is a utility that removes plans that reference changed objects from the query plan logging table.
Later on, I am going to ask for contest votes but only if you think it is the best submission. Download here.
P.S. Whooh…. Now that my vacation is over and this is submitted, I have whole backlog of posts to write. As a teaser, one of these will be an discussion eFight with my BrentO. He is sooooooooooo pwnt and he doesn’t even realize it yet. hehe. Stay tuned.
Captain Varchar(max) and the Pagelatch Posse 6
This post was inspired by this MSDN forum question and with nudge from BrentO.
WordPad FTW!
Filtered Indexes VS. Indexed Views
Note: This is a simple example and YMMV. I may test queries with lookups, hashes, aggregations etc later. Let me know if you have an interesting test case.
I decided to run a quick test comparing covering filtered indexes vs. covering indexed views vs. a normal covering index. Read performance-wise, it is a statistical draw.
The DDL and queries
USE [demodb]
GO
CREATE TABLE [dbo].[tblItem](
[Itemid] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](100) NULL,
[Datecreated] [datetime] NULL,
[Closed] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Itemid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
create index ix01 on tblitem(Datecreated, ItemName)
create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22'
go
create view ivw01
with schemabinding
as
select ItemName, Itemid from dbo.tblItem
where Datecreated > convert(datetime, '2007-08-22', 101)
create unique clustered index ix01 on ivw01(itemid)
create index ix02 on ivw01(ItemName)
set statistics io on
--Using a normal covering index
select ItemName, Itemid from tblItem with (index=ix01)
where Datecreated > convert(datetime, '2007-08-22', 101)
--using a filtered index
select ItemName, Itemid from tblItem with (index=ix02)
where Datecreated > convert(datetime, '2007-08-22', 101)
--using an indexed view
select * from ivw01
The graphical query plans
There are no surprises in the query plan. It does a scan on the filtered index but that doesn’t matter since it is …err… filtered.
Statistics IO Output
Normal covering index:
(346129 row(s) affected)
Table ‘tblItem’. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Filtered Index:
(346129 row(s) affected)
Table ‘tblItem’. Scan count 1, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Indexed view:
(346129 row(s) affected)
Table ‘tblItem’. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Other Considerations
Some other things to consider is the fact that the filtered index is a fraction of the size of the normal index. The indexed views are an enterprise feature but filtered indexes are not. The optimizer may have a hard time choosing a filtered index but these problems are not present with indexed views. So the right choice may vary with different apps even if the schema is the same.
My RSS
-
My LifeStream
My Presence
