Entries for the 'tsql' Category

Wiki, Wiki, Wiki

BTW, did you know I am an editor for the TSQL code section over at http://sqlserverpedia.com/wiki/Transact_SQL_Code_Library. I would have announced it sooner but I hadn’t actual done anything yet with the job drama and holidays. Welp, the time has come. I have uploaded several DMV queries and I am about to go over the other submissions. On top of that, I will be participating in a web cast on it. We will get to that in a minute.

I was wondering how this whole wiki thing would work out. I mean every time I hear wiki, I think of Newcleus. Why not just post stuff here. Well, I will probably do that too when I run across something cool.

Here is what makes the wiki cool:

• It is open for peer enhancement, addons and bugfixes.
• It is community driven.
• It is well organized.
• It has already gained support from leaders in the community.
• The Quest marketing team.

Here are the details about the webcast I mentioned:

ctrl+v via BrentO

On Thursday, December 11th at 8am Pacific, 11am Eastern, I’ll be doing a live video webcast with some of my fellow SQLServerPedia editors:

• Denny Cherry, Performance Tuning Editor
• Greg Low, Architecture & Configuration Editor
• Jason Massie, T-SQL Code Library Editor

We’re going to talk about what we’re doing, why we’re doing it, and how you can help.  I’ll even create a new Wiki article and edit an existing Wiki article in real time, in front of everybody, to show how it works and how easy it is. (Watch it not work, hahaha.)

Sign up for the SQLServerPedia Webcast

To quote Tim Ford, “I will be the dumbest guy in the room” :) It should be fun though!

email it! |   |   | 

Permalink     2 Comments

This Update Came from SQL Server

Disclaimer:

Just because you can does not mean you should and even if you should, use with care. For example, you will have no friends after a day or so of updating your facebook status every time a tlog backup runs on your farm. There are also many security concerns to consider. That said, I can think of a few uses for SQL Server Web 2.0 mashups and I am not all that creative.

How it works

Sign up at http://ping.fm and then sign up at whatever services you want to update from SQL. Here is a list of what they currently support. From SQL, we could send a message to twitter or even better, yammer via database mail. Here is the obligatory, “Don’t send anything confidential over email”

Possible Uses

Augmenting your monitoring – The keyword is word is “augmenting” except for the smallest installations. For the crown jewel of the company, you might get a call from the 24/7 staff and an email. Why not a tweet too especially if it can add SMS functionality that you may not currently have?

Turn X into an RSS feed – Where X could be, all job executions, any SEV error messages, any trace event or DDL operations, or [your idea here]. You can funnel the RSS into the intranet, your reader or [insert creative idea here].

An insert trigger on your press release table – Blast it to the world. Some companies are already doing similar things http://dell.com/twitter.

Personal updates from the SQL Editor – I don’t know about you but I have spent weeks on end in tsql. Some deadlines don’t allow for time to come up for air. It would pretty cool in a geeky way to tell the world that I just replaced 500 lines of cursor code with a 10 liner or whatever without leaving SSMS.

Example Code:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Ping.fm demo',

    @description = 'Mail account',

    @email_address = 'jason@statisticsio.com',

    @replyto_address = 'jason@statisticsio.com',

    @display_name = 'Jason Massie',

    @mailserver_name = 'mail.statisticsio.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MailAccount',

    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MailAccount',

    @account_name = 'Ping.fm demo',

    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MailAccount',

    @principal_name = 'public',

    @is_default = 1 ;


-- here is the magic blasphemy.
EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'

I just updated twitter, linkedin, facebook, plurk, rejaw from SQL!

In closing

One more time, pay attention to security. There are a few commercial products on the horizon. http://ping.fm and most of these service also have API’s that may be a better alternative than database mail. Finally, database mail was meant for administrative alerts and I suggest you stick with that mindset..

email it! |   |   | 

Permalink     2 Comments

This week

I am in a what feels like a whirl wind tour of the globe so content may be light this week. Jetlagged in Denmark right now and should seriously be asleep. I have some interesting topics in the works but until then I have "suggested reading" at several places.

FriendFeed:

http://friendfeed.com/statisticsio

Google Reader Shared Items:

http://www.google.com/reader/shared/09956560379006770135

Facebook:

http://www.facebook.com/profile.php?id=1013730310&ref=profile

Google reader is probably the most SQL focused link. The others have google reader shares + stuff. If you are reading this, chances are you will find what I find cool at least slightly interesting as well.

email it! |   |   | 

Permalink     0 Comments

"Adhoc Triggers"

This is a feature that I didn't even know existed until the recent SQL Server 2008  beta tests even though it was introduced in SQL Server 2005. So.... I wanted to pass it on.

Rob Farley:

OUTPUT clause - knowing what goes in, and what you've accidentally taken out.

Scott on Writing

OUTPUTing Data from the Just-Inserted, Updated, or Deleted Row(s)

Check it out and I believe there is room for improvement if there is demand so make requests at connect.

email it! |   |   | 

Permalink     1 Comments

This Post Needs More Stored Procedures

New in SQL Server 2008 is a server setting called “Optimize for adhoc workloads”. I was happy to see this. You should be too especially if you have ever had arm wrestle an app that causes a bloated proc cache on an x86 box. Ugghh… Adam blogged on it here, Bob blogged on it here and here is the documentation.

I was wondering how this setting would play with the forced parameterization database setting. It looks like forced parameterization trumps the new server wide setting. If my simple tests below are right, it could present an interesting problem. What if your app generates a lot of totally unique adhoc queries that have no benefit from parameterization and you have queries that would benefit from parameterization? Well, the answer is still the same. Write stored procedures. :)

In the real world, sometimes you get a pig and all you can do is put lipstick on it. If you have to choose, “Optimize for Adhoc” would most benefit memory\IO bound servers while “forced parameterization” could help both the CPU and memory usage. However, that is a very general statement. Other things should be considered so it will depend.

--So what happens when you turn on "Optimize for adhoc" and forced parameterization 
exec sp_configure  'show advanced options', 1
reconfigure with override
go
exec sp_configure 'optimize', 1
reconfigure with override 
alter database master set parameterization forced
dbcc freeproccache 

--Let's run a query will not generate a trivial plan.
select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go

--The CacheObjType is a compiled plan and 57344 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

--Now, let’s turn off forced parameterization

alter database master set parameterization simple
dbcc freeproccache

select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go 

--The CacheObjType is a Compiled Plan Stub and 320 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

email it! |   |   | 

Permalink     4 Comments

Buffer Pool Usage by Database

Here is a quick query I wrote today. It is the first time I had to go to this DMV so I thought I would share. It would be useful when planning for consolidation and troubleshooting a bunch of apps that have been consolidated or are hosted in a shared environment. Once you find the database, you can break it down by object and index with Tom Davidson's query.

select db_name(database_id) as dbName, count(*)*8/1024 as BufferPoolMB

from sys.dm_os_buffer_descriptors

group by db_name(database_id)

order by 2 desc

email it! |   |   | 

Permalink     1 Comments

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 ADDCONSTRAINT [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