Non-clustered indexes on 1 separate fileGroup?

Hi Guys,

I am reviewing the storage structure of my growing db.

I am going to create multiple FileGroups and db files.
Most of the filegroups will be used to allow table partitioning and put the heavily used partitions on different storage than the read-only archives. TempDB will also have its own filegroup/storage.

For Indexes, I have a doubt. I am sure I can have all my non-clustered indexes on a separate fileGroup and storage but what about primary keys and/or clustered indexes?

Should I move them to the same filegroup than the non-clustered indexes or should I leave them with the data? What would be best?

I am running a 100GB reporting datamart on SQL2005 enterprise, There are almost no transactions, just a daily and weekly refresh. I will soon have more storage, SAN or NetApp, not sure yet.

Thanks,

Philippe

Hi

Moving your indexes to a seperate filegroup can improve the parallism of some querries that need to work with indexes and then do a bookmark lookup afterwards. If you have a VLDB like you said you should consider doing it, since the indexes cant be kept in memory all the time. So when you access the indexes it will not block the retrieval of your data from the other partition.

NOLOCK on views

Hey guys,

I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
SELECT v1.xxx, v2.yyy
FROM dbo.vw_SomeView v1 WITH (NOLOCK)
INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?

I've looked in the BOL and searched on the net but can't find anything on this particular topic.

Lex

PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question ;)Looks like it's time for a little hands on experiment. Take an update lock on one of the tables used in either of the views in one QA window, and try to run the sql in another.|||Looks like it's time for a little hands on experiment. Take an update lock on one of the tables used in either of the views in one QA window, and try to run the sql in another.

I use and recommend (NOLOCK) Optimizer hints on a regular basis. Just know that when a (NOLOCK) hint is used, it performs a "Dirty Read" against the data.

The primary benefit to a (NOLOCK) hint is to prevent the blocking of objects from occurring when users are selecting data. I would recommend using them if you have contention in your environment with users holding exclusive locks on tables.

Hope this helps!

Nofications assemblies not found with .net 2.0

Hi guys,

I was trying to develop a non hosted event provider and decided to use NS event object api to submit events to NS. My development system has VS 2005 and my plan is to connect to a NS instance running on a machine on the local network.

But it is weird that I could not find "Microsoft.SqlServer.NotificationServices.dll" in my dev system. Does it mean to develop NS applications we need to have VS 2005 and Sql Server 2005 on same box.

Also note that I have sql server express edition on my development machine and I am aware that express editions doesn't support notification services.

Thanks,

Shamir

Hi Ahmed -

You need to install the SQL Server 2005 Notification Services Client

Components on the development machines.

http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en

Only Standard and Enterprise Editions support SSNS.

http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx

HTH...

Joe

--

Joe Webb

SQL Server MVP

http://www.sqlns.com

~~~

Get up to speed quickly with SQLNS

http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.

(www.sqlpass.org)

On Sun, 23 Jul 2006 02:47:01 -0700,

wrote:

>Hi guys,

>

>I was trying to develop a non hosted event provider and decided to use

>NS event object api to submit events to NS. My development system has VS

>2005 and my plan is to connect to a NS instance running on a machine on

>the local network.

>

>But it is weird that I could not find

>"Microsoft.SqlServer.NotificationServices.dll" in my dev system. Does it

>mean to develop NS applications we need to have VS 2005 and Sql Server

>2005 on same box.

>

>Also note that I have sql server express edition on my development

>machine and I am aware that express editions doesn't support

>notification services.

>

>Thanks,

>

>Shamir

>

>|||

[Reposting since my prior post didn't seem to render very well.]

Hi Ahmed -

You need to install the SQL Server 2005 Notification Services Client
Components on the development machines.
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en

Only Standard and Enterprise Editions support SSNS.
http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx

HTH...

Joe

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)