Ineeficient Memory Usage

I have an instance of SQL Server 2000 (SP4) running on Win 2K.
On it I have a task that runs to decompose xml into tables using
sp_prepare/sp_remove xml doc procedures and open xml.
When the server is booted or sql server and sql server agent process
restarted it sits happily using about 60MB of memory. However once I start my
xml decomposition task the memory gradually creeps up until it hits the
maximum.
Now I know SQL server will effectively grab all the memory it can and keep it.
However my issue is not with the amount of memory it has taken, but the way
it would appear to use it.
This is because when the task first kicks off it runs like grease lightening
and will decompose about 1000 1MB XML files a minute. However once the memory
has reached maximim this is down to 1 1MB XML file a minute. Quite a
significant difference despite the file being indetical in structure (they
are messages with different content).
I have tried to use profiler to determine where the problem is, but it looks
most likely that sql server is not releasing enough unused memory back to
itself. The overhead is in the passing of large chunks of data between stored
procedures and the use of the xml (sp_prepare and sp_remove) and this is only
a significant overhead once SQL Server has taken all the memory for itself.
Also once the task is stopped at no point is memory ever released by SQL
Serevr even if there is no activity at all for hours/days.
Is this a known issue/bug or is it possible that I have done something wrong?
Note sp_remove is always called after an sp_prepare so I assume that this
should unallocate any memory used by that.
Alternatively if a parent procedure runs continually calling the same child
procedure, do the variables local to the child procedure get cleared properly
between each call if the parent is still active?
I'm very perplexed and would greatly appreciate some assistance/ideas
Cheers
Tom
Have I posted this in the wrong place or is my wording too poor for anyone to
assist?
I had hoped to see some replies by now.
Please feel free to state the obvious as I may have missed something simple.
Cheers
Tom
"TomPearson" wrote:

> I have an instance of SQL Server 2000 (SP4) running on Win 2K.
> On it I have a task that runs to decompose xml into tables using
> sp_prepare/sp_remove xml doc procedures and open xml.
> When the server is booted or sql server and sql server agent process
> restarted it sits happily using about 60MB of memory. However once I start my
> xml decomposition task the memory gradually creeps up until it hits the
> maximum.
> Now I know SQL server will effectively grab all the memory it can and keep it.
> However my issue is not with the amount of memory it has taken, but the way
> it would appear to use it.
> This is because when the task first kicks off it runs like grease lightening
> and will decompose about 1000 1MB XML files a minute. However once the memory
> has reached maximim this is down to 1 1MB XML file a minute. Quite a
> significant difference despite the file being indetical in structure (they
> are messages with different content).
> I have tried to use profiler to determine where the problem is, but it looks
> most likely that sql server is not releasing enough unused memory back to
> itself. The overhead is in the passing of large chunks of data between stored
> procedures and the use of the xml (sp_prepare and sp_remove) and this is only
> a significant overhead once SQL Server has taken all the memory for itself.
> Also once the task is stopped at no point is memory ever released by SQL
> Serevr even if there is no activity at all for hours/days.
> Is this a known issue/bug or is it possible that I have done something wrong?
> Note sp_remove is always called after an sp_prepare so I assume that this
> should unallocate any memory used by that.
> Alternatively if a parent procedure runs continually calling the same child
> procedure, do the variables local to the child procedure get cleared properly
> between each call if the parent is still active?
> I'm very perplexed and would greatly appreciate some assistance/ideas
> Cheers
> Tom
>
|||Have I posted this in the wrong place?
I had hoped for some replies by now.
If my wording is poor or unclear I am will hapily clarify as I could really
do with some advice on this issue.
Also don't be afraid to state the obvious as I may have missed something
really simple.
Cheers
Tom
"TomPearson" wrote:

> I have an instance of SQL Server 2000 (SP4) running on Win 2K.
> On it I have a task that runs to decompose xml into tables using
> sp_prepare/sp_remove xml doc procedures and open xml.
> When the server is booted or sql server and sql server agent process
> restarted it sits happily using about 60MB of memory. However once I start my
> xml decomposition task the memory gradually creeps up until it hits the
> maximum.
> Now I know SQL server will effectively grab all the memory it can and keep it.
> However my issue is not with the amount of memory it has taken, but the way
> it would appear to use it.
> This is because when the task first kicks off it runs like grease lightening
> and will decompose about 1000 1MB XML files a minute. However once the memory
> has reached maximim this is down to 1 1MB XML file a minute. Quite a
> significant difference despite the file being indetical in structure (they
> are messages with different content).
> I have tried to use profiler to determine where the problem is, but it looks
> most likely that sql server is not releasing enough unused memory back to
> itself. The overhead is in the passing of large chunks of data between stored
> procedures and the use of the xml (sp_prepare and sp_remove) and this is only
> a significant overhead once SQL Server has taken all the memory for itself.
> Also once the task is stopped at no point is memory ever released by SQL
> Serevr even if there is no activity at all for hours/days.
> Is this a known issue/bug or is it possible that I have done something wrong?
> Note sp_remove is always called after an sp_prepare so I assume that this
> should unallocate any memory used by that.
> Alternatively if a parent procedure runs continually calling the same child
> procedure, do the variables local to the child procedure get cleared properly
> between each call if the parent is still active?
> I'm very perplexed and would greatly appreciate some assistance/ideas
> Cheers
> Tom
>
|||Hi Tom
This is the right newsgroup. But since it is a newsgroup, it may take a
while until your posting gets distributed and people answer.
There are lots of reasons why this may happen, including a problem with SQL
Server. Are there any other processes going on in the database that may use
up memory and resources (other queries, allocating temp tables etc)?
Can you send us a repro, so that we can investigate it in our lab to see
whether it repros and if, why?
Thanks
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...[vbcol=seagreen]
> Have I posted this in the wrong place?
> I had hoped for some replies by now.
> If my wording is poor or unclear I am will hapily clarify as I could
> really
> do with some advice on this issue.
> Also don't be afraid to state the obvious as I may have missed something
> really simple.
> Cheers
> Tom
> "TomPearson" wrote:
|||I have tried this in isolation without any other db queries, processes etc
going on and the result is the same. No temp tables are used during this task.
I would be eager to have someone look into the problem, but I don't
understand what you mean by a repro I'm afraid.
Cheers
Tom
"Michael Rys [MSFT]" wrote:

> Hi Tom
> This is the right newsgroup. But since it is a newsgroup, it may take a
> while until your posting gets distributed and people answer.
> There are lots of reasons why this may happen, including a problem with SQL
> Server. Are there any other processes going on in the database that may use
> up memory and resources (other queries, allocating temp tables etc)?
> Can you send us a repro, so that we can investigate it in our lab to see
> whether it repros and if, why?
> Thanks
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...
>
>
|||A repro is a T-SQL script, data and (if necessary) some code that shows the
behaviour so we can try to run it in our environment to see whether we can
find the reason for the behaviour (as per email exchange).
Best regards
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...[vbcol=seagreen]
>I have tried this in isolation without any other db queries, processes etc
> going on and the result is the same. No temp tables are used during this
> task.
> I would be eager to have someone look into the problem, but I don't
> understand what you mean by a repro I'm afraid.
> Cheers
> Tom
>
> "Michael Rys [MSFT]" wrote:
|||Michael,
I have similler issue with the MSXML parser with Visual Studio. Here I have
a sample code. Can you run this and see you find any thing worng with my code.
thanks in advance.
#include "stdafx.h"
#include <msxml2.h>
#include "comutil.h"
#include "psapi.h"
void Init();
void Release();
BOOL LoadXML(LPSTR lpzxml);
BOOL Go_To_Parent(LPCSTR lpszNodeName);
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue);
void PrintMemoryInfo( DWORD processID, int Iteration );
IXMLDOMDocument2*pXMLDom;
IXMLDOMNode*pNode;
int _tmain(int argc, _TCHAR* argv[])
{
LPSTR lpzxml =
"<PromptDataRoot><Errors>OK</Errors><NodeId>3</NodeId><Action></Action><SessionDataRoot><SessionId>001050625171432 00002</SessionId><DateTime>6/25/2005
5:14:37
PM</DateTime><CurrentDataItem>TNI</CurrentDataItem><LookupUserInputKey>1</LookupUserInputKey><DataCollected><InitialUserKey> 1</InitialUserKey><LANGUAGECHOICE>1</LANGUAGECHOICE></DataCollected><StepLog><PromptData><NodeId>2</NodeId><UserInputKey>1</UserInp
utKey><LookupKey>1</LookupKey></PromptData><Action></Action></StepLog></SessionDataRoot><PromptControlData><Language>0</Language><MainMessage><MsgId>!102</MsgId><ReplayMsgKey></ReplayMsgKey><CorrectValues></CorrectValues><CharSet>0123456789</CharSet><Leng
th>10</Length><Timeout>10000</Timeout><TimeOutMsgId>!102</TimeOutMsgId><RetryCount>3</RetryCount><RetryExitAction><Transfer></Transfer><End>N</End></RetryExitAction><RetryExitMsgId>!125</RetryExitMsgId></MainMessage><ConfirmMessage><MsgId>!103
^# !104</MsgId><CorrectValues>1,2
</CorrectValues><CharSet></CharSet><Length>1</Length><Accept>1</Accept><Reject>2</Reject><Timeout>10000</Timeout><TimeOutMsgId>!103
^#
!104</TimeOutMsgId><RetryCount>2</RetryCount><RetryExitAction><Transfer><Record>N</Record><Phone>2003207</Phone><BusyAction>End</BusyAction><BusyMsgId>!123</BusyMsgId></Transfer><End>N</End><CompletionStatus>1</CompletionStatus></RetryExitAction><RetryExi
tMsgId>!126</RetryExitMsgId></ConfirmMessage></PromptControlData><SpecialProcessing><MiscMessageI dTags></MiscMessageIdTags></SpecialProcessing></PromptDataRoot>";
char szValue[512];
DWORD x;
for(int i = 0 ;i<1000;i++)
{
Init();
LoadXML(lpzxml);
Go_To_Parent("PromptDataRoot");
GetChild("Errors", szValue);
Release();
x = GetCurrentProcessId();
PrintMemoryInfo(x, i);
Sleep(10000);
}
MessageBox(NULL, "Done", "Info", MB_OK);
return 0;
}
void Init()
{
CoInitialize(NULL);
CoCreateInstance(__uuidof(DOMDocument40),
NULL,
CLSCTX_INPROC_SERVER,
__uuidof(IXMLDOMDocument2),
(void**)&pXMLDom);
pXMLDom->put_async(VARIANT_FALSE);
pXMLDom->put_validateOnParse(VARIANT_FALSE);
pXMLDom->put_resolveExternals(VARIANT_FALSE);
}
void Release()
{
if (pNode)
pNode->Release();
if (pXMLDom)
pXMLDom->Release();
CoUninitialize();
}
BOOL LoadXML(LPSTR lpzxml)
{
VARIANT_BOOL status;
BSTR bstrXML;
try{
bstrXML = _bstr_t(lpzxml);
pXMLDom->loadXML(bstrXML, &status);
return TRUE;
}
catch(...)
{
return FALSE;
}
}
BOOL Go_To_Parent(LPCSTR lpszNodeName)
{
IXMLDOMElement *pElementRoot;
IXMLDOMNodeList *pNodeList;
BSTRbstrValue;
long lNodeLength;
try{
pXMLDom->get_documentElement(&pElementRoot);
pElementRoot->getElementsByTagName(_bstr_t(lpszNodeName), &pNodeList);
pNodeList->get_length(&lNodeLength);
if (lNodeLength > 0)
{
pNodeList->get_item(0, &pNode);
return TRUE;
}
else
{
pXMLDom->get_childNodes(&pNodeList);
pNodeList->get_item(0, &pNode);
pNode->get_xml(&bstrValue);
return TRUE;
}
}
catch(...)
{
return FALSE;
}
}
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue)
{
BSTR bstrValue;
long lLength, l;
IXMLDOMNodeList *pNodeList;
IXMLDOMNode *pLocalNode;
try{
pNode->get_childNodes(&pNodeList);
pNodeList->get_length(&lLength);
for (l=0;l <=lLength;l++)
{
pNodeList->get_item(l, &pLocalNode);
pLocalNode->get_xml(&bstrValue);
pLocalNode->get_nodeName(&bstrValue);
if (strcmp((LPSTR)_bstr_t(bstrValue), lpszNodeName) == 0)
{
pLocalNode->get_text(&bstrValue);
sprintf((char *)lpszNodeValue, "%s", (LPCSTR)_bstr_t(bstrValue));
return TRUE;
pNodeList->Release();
pLocalNode->Release();
}
}
return FALSE;
}
catch(...)
{
return FALSE;
}
}
void PrintMemoryInfo( DWORD processID, int Iteration )
{
HANDLE hProcess;
PROCESS_MEMORY_COUNTERS pmc;
TCHAR szProcessName[MAX_PATH] = TEXT("<unknown>");
hProcess = OpenProcess( PROCESS_QUERY_INFORMATION |
PROCESS_VM_READ,
FALSE, processID );
if (NULL == hProcess)
return;
HMODULE hMod;
DWORD cbNeeded;
if ( EnumProcessModules( hProcess, &hMod, sizeof(hMod),
&cbNeeded) )
{
GetModuleBaseName( hProcess, hMod, szProcessName,
sizeof(szProcessName)/sizeof(TCHAR) );
}
if ( GetProcessMemoryInfo( hProcess, &pmc, sizeof(pmc)) )
printf( "Iteration :%d, ProcessName :%s, PID:%u, Memory Usage:%dk
\n",Iteration, szProcessName, processID ,pmc.WorkingSetSize/1024);
CloseHandle( hProcess );
}
"Michael Rys [MSFT]" wrote:

> A repro is a T-SQL script, data and (if necessary) some code that shows the
> behaviour so we can try to run it in our environment to see whether we can
> find the reason for the behaviour (as per email exchange).
> Best regards
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...
>
>

Indirect Configuration with ConfigType 'Indirect SQL Server' fails

Hi,

I have a package that uses a Configuration of type SQL Server where the property values are held.

This runs successfully using this direct configurations.

When I use an Indirect configuration using an environment variable to point to this SQL Server configuration type the package won't even validate.

The Indirect Configuration is:

[EHC-SQLD-01.].[SSISConfigsDEV];[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

which follows the standard of : db connections, config table, filter

This works by the way on my client but on the dev server the error is:

Error: The connection "[EHC-SQLD-01].SSISConfigsDEV" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I've tried every combination for the env variable using quotes, full computer name etc (I thought it was the hyphens in the name), but can't seem to get it to work.

I've also tried as both user and system env variables but made no difference (which one should we use for SSIS anyway as BOL doesn't state this ?)

Appreciate any help. I'm trying to deploy this on the Dev server for testing.

Thanks

P R W.

P R W,

I have never used the Indirect method for a SQL Server based configuration; but I use a similar approach that involves an env variable and the direct method:

1. Set the SQL server based configuration using the direct method using a connection manager called, Let's say 'Configuration'

2. Create an Env variable based configuration to set the connection string of 'Configuration' connection manager and place it at the very top of the configuration organizer, so it happens before the SQL server based one.

Make sure you create the Env variable wit the proper connection string for ‘Configuration’; and that you close and re-open BIDS so you can see the Env variable in thedropdow list for step 2.

I started using this method a while before knowing about indirect configurations and it has worked fine; maybe that is why I have not looked into the indirect ones.

|||

I'm not sure why you would want to do that.

If you deploy the package to different environments e.g. from dev to producution, you must still go into the package to change the Env variable to amend the connection string as eg. the package will now sit on a different server.

Also, you env variable sits first so the configs used will actually be the configs that run last i.e. the SQL Server based one, so what's the point of having two configs that do the same thing ?

The point of indirect configs is that you don't have to open the package. All you do is amend the Environment variable defined on the OS. This should make package deployment easier and more secure.

The actual problem lies in the fact that the Package does not recognise the Indirect config for the environment variable. Although I have successfully set this up on my client, it is not working for the Dev server.

|||

P R W wrote:

I'm not sure why you would want to do that.

If you deploy the package to different environments e.g. from dev to producution, you must still go into the package to change the Env variable to amend the connection string as eg. the package will now sit on a different server.

I guess I was not clear enough. You DO NOT have to open the package and change the Env Variable name; you have to make sure all servers you want to deploy the solution to have the same env variable (same name); then you need to make sure the connection string in the Env variable is right. That's exactly how the same way the Indirect configurations works; you reference a Env variable and then you just change its value, right?

P R W wrote:

Also, you env variable sits first so the configs used will actually be the configs that run last i.e. the SQL Server based one, so what's the point of having two configs that do the same thing ?

Ok let's try again. First, I don't have 2 configurations doing the same thing. Let me give you an scenario: My package has about 10 component/properties that need to be configured at run time; so I create a SQL Server configuration table with all those entries and define same number of SQL server configurations on every of those properties using the direct method. Since I am using the direct method I am asked to provide connection information to get access to the configuration table; is at that point where I use the 'configuration' connection manager. So far, I have create my 10 configurations based on a SQL Server table; to get access to that configuration table I am using 'Configuration' connection manager; the problem with that is that the connection string of 'configuration' needs to be changed at run time depending on which server/Environment is the package being executed. It is at this point when I create an Env. variable to set the connection string of 'configuration' and place it at the very top. That way on each run SSIS will go to the Env variable; will take the connection string, set it to 'configuration' and then all other sql server based configurations will use the right connection to get access to the table.

Notice that an Env variable with the same name needs to exists on every server your are deploying the solution to. But the same constraint exists using the indirect method. I can tell you more; using the indirect method you are constraint to use only Env variable; with this approach you could use alternatively an XML configuration file; that comes handy when you hit the wall when some weird IT policies that prevent you using Env variables

If you don't believe just give it a try; it will take just a couple of minutes to run a test.

P R W wrote:

The point of indirect configs is that you don't have to open the package. All you do is amend the Environment variable defined on the OS. This should make package deployment easier and more secure.

That is exactly what I am doing; just change a value in a Env variable.

P R W wrote:

The actual problem lies in the fact that the Package does not recognise the Indirect config for the environment variable. Although I have successfully set this up on my client, it is not working for the Dev server.

That is why I started my first post saying that I have not used the indirect method; and that I was offering an alternative approach that has worked for me.

|||

I still don't seem to follow what you are saying, as I'm confused by the terminology you're using.

If I have followed it correctly (and I must admit I had to read it several times through - maybe cos its a Fri and everyone else has gone home )., this is the scenario I have:

This is what I have set in Package Configs (this is my direct SQL Server Config):

Configuration Name : Configuration1

Configuration Type: SQL Server

Configuration String : EHC-SQLD-01.SSISConfigsDEV;[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

(The table [SSIS Configurations DEV] holds all the necessary properties and values).

So you are now saying, create another Package Configuration using Config Type of Environment Variable. ?

Type in a name - any name (lets say 'EnvVarTest') and set the Property of that variable to the Connection string of Configuration1 ?

If I've read that correct, when setting up the Env Variable on the OS, the Env variable name is X and Value 'EnvVarTest'. ?

I'm not sure that is what you are saying though, because you would still need to edit the package when deploying from one machine to another ?

Regards,

P R W.

|||

P R W wrote:

I still don't seem to follow what you are saying, as I'm confused by the terminology you're using.

If I have followed it correctly (and I must admit I had to read it several times through - maybe cos its a Fri and everyone else has gone home )., this is the scenario I have:

This is what I have set in Package Configs (this is my direct SQL Server Config):

Configuration Name : Configuration1

Configuration Type: SQL Server

Configuration String : EHC-SQLD-01.SSISConfigsDEV;[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

(The table [SSIS Configurations DEV] holds all the necessary properties and values).

So you are now saying, create another Package Configuration using Config Type of Environment Variable. ?

Type in a name - any name (lets say 'EnvVarTest') and set the Property of that variable to the Connection string of Configuration1 ?

If I've read that correct, when setting up the Env Variable on the OS, the Env variable name is X and Value 'EnvVarTest'. ?

I'm not sure that is what you are saying though, because you would still need to edit the package when deploying from one machine to another ?

Regards,

P R W.

Sorry if it sounded too confussing.

See if this clarifies it:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

Otherwise, I will give up

|||

OK, now I followed that. That was useful and solved the problem by using Direct config rather than Indirect Configuration.

I wasn't setting up the Environment Variable value correctly on the OS.

Appreciate the time you've taken to help.

(You really weren't thinking of giving up though were you ).

Still unsure as to why the Indirect Configuration failed to validate when it worked correctly on local machine. Tried all sorts of combinations for the Configuration. Anyway that's for another day.

Thanks.

P R W.

|||

PRW,

Glad you found it helpful. I also took the time to research about the indirect configurations, and I think I understood how the work, thanks to a blog post I found. Basically, the Env variable has to contain the SSIS connection manager to be used, the configuration table name, and the configuration filter. Saying that, it looks like you have to create an Env. variable for every property you want to override at run time; which make me wonder if using plain Env variable based configuration would not give the same results in a more simpler way.

The workaround I described in my blog requires only one Env variable only; then all configuration values are stored in the table.

Here is the link, in case you want to look into that other post hat talks about SQL Server Indirect configurations:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

|||

You don't have to have an Env variable for every property required to by dynamic.

Basically it works in a similar way as you have used Direct configs eg. you setup a SQL Server Config type with table containing all properties required to be dynamic and their values. Then you set this up as Indirect config in Package configs. Then setup an env variable on the OS, that makes a connection to the SQL Server Config type Package config with value:

Servername.DBname;Tablename;Filtervalue;

so as you see very similar to the methodology you have used.

I have actually used it successfully as I said in my local environment and it works well. You don't need to reopen the package and all configs can be set in any of the other config types such as SQL, XML.

|||

P R W wrote:

You don't have to have an Env variable for every property required to by dynamic.

Basically it works in a similar way as you have used Direct configs eg. you setup a SQL Server Config type with table containing all properties required to be dynamic and their values. Then you set this up as Indirect config in Package configs. Then setup an env variable on the OS, that makes a connection to the SQL Server Config type Package config with value:

Servername.DBname;Tablename;Filtervalue;

so as you see very similar to the methodology you have used.

I have actually used it successfully as I said in my local environment and it works well. You don't need to reopen the package and all configs can be set in any of the other config types such as SQL, XML.

I think I am missing something here. If the Environment variable requires 'FilterValue' as part of its value; how can you set multiple values/properties at run time using only one Env variable? Can you provided a list of values for the 'Filtervalue' part?

|||

The Filter Value you provide in the OS Env variable, is used to determine the value held in the column 'ConfigurationFilter' of the SQL Server table that has been defined as the SSIS Configuration.

This table also has cols of :

ConfiguredValue - the actual property/variable value etc.

Package Path - the actual property/variable etc.

Configured Value Type.

The ConfigurationFilter is just a value I believe that determines what SSIS Configuration that the values in the table belong too. eg. I would have a table as below containing my SSIS Configs for a package named 'pkgLRD CED Import'

pkgLRD CED Import Void \Package.Variables[User::VarUnencryptionArguments].Properties[Value] String
pkgLRD CED Import C:Test\ \Package.Variables[User::VarSourceConnection].Properties[Value] String
pkgLRD CED Import C:\pkgLRD CED Import.chk \Package.Properties[CheckpointFileName] String

It made sense to me to use the Package name (since this ideally should be unique) though you could actually use any value. Thus you could hold all the Config values for all Package configs in one central table. The Filter value (in this case package name) therefore determines which configs the package should pick up.

So the OS Env variable would look like:

Servername.Dbname;Configtablename;ConfigFilter(i.e.packagename);

This worked for me although I must admit I haven't tested it for multiple packages yet, only one package.

The problem is as you have sort of stated that you would need one OS Env Variable for every package (though not every property).

This could possibly become a bit of a mess.

You could just have one OS Env variable and not define the Config Filter (haven't tested this though), but then I believe you would have to have a separate table for each package. This maybe a requirement or maybe not.

This is why I actually prefer your solution using the Direct method that you have since explained. It allows me to have one single OS env variable on each Server, and one central table for all SSIS package configs.

Regards,

P R W.

BTW I'm still learning SSIS (5 months in) so don't consider myself any sort of expert.

Indexing job interferes with log shipping

The log shipping job fails right after indexing runs and the log file itself
turns up corrupt. Is it possible to run a command that temporarily disables
the log shipping while indexing runs and then upon completion of the indexing
(fail or not) re-enable the log-shipping job?
Regards,
Jamie
No, but it is recommended that you switch to bulk logged recovery model
before starting indexing as the log size is smaller.
Return to full after you have completed the indexing operation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:84589C1C-6598-4985-81F8-61A68813EA7D@.microsoft.com...
> The log shipping job fails right after indexing runs and the log file
> itself
> turns up corrupt. Is it possible to run a command that temporarily
> disables
> the log shipping while indexing runs and then upon completion of the
> indexing
> (fail or not) re-enable the log-shipping job?
> --
> Regards,
> Jamie
|||Paul,
I think I should have mentioned that this is partially a Red Gate error:
Data decompression error: Access violation at address 0050BA10 in
module'SQBCoreService.exe'. Read of address 7FF80000 Process terminated
unexpectedly. Error code: -2139684860
AND ON THE Application Level...
The description for Event ID '-1073724769' in Source 'MSSQLSERVER' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the event:'18210',
'BackupVirtualDeviceSet::Initialize: Open failure on backup device
'SQLBACKUP_C2F85077-1436-4FC9-BBD0-7B4ECC822724'. Operating system error
0x80070002(The system cannot find the file specified.).
Red Gate suspects that this happens when the decompression routine is
running its copy operation. I didn't know this when I asked the question.
I thought it might be something to do with a locking issue but Hilary gave me
enough information for me to figure it out by myself.
Instead of trying to alter the logging job, I alter the indexing job to
switch to BULK_LOGGING mode and then return to FULL when the indexing is
done. Will let you know next week if it works or not.
Jamie
Regards,
Jamie
"Paul Ibison" wrote:

> Jamie - how does the log file 'turn up corrupt' - can you post any error
> message you're seeing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Indexing Advice

Hi guys,
I've been playing with this query for about a week now, and I still
can't find ways to speed it up. It runs for about 5 minutes. The only
table (among the 41) with significant amount of data is dbo.S_EVT_ACT,
which contains about 900,000 records. The query is automatically
generated by Siebel, and therefore not really modifiable. And hints
are not really possible. While looking at the exec plan, we can see
that what takes so long is a clustered index scan of T1. This
clustered index is defined on a column ROW_ID (a Siebel-generated
primary key used for every table). I tried to define all kinds of
indexes in order to bypass this clustered index scan, but no success
so far ... Here's the query:

SELECT ...
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T2 ON T1.ROW_ID =
T2.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_FN_APPR T3 ON T2.AMS_ACT_ID =
T3.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.PERFRM_BY_OU_ID =
T4.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.PRI_LST_ID =
T5.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T6 ON T1.PR_CON_ID =
T6.PAR_ROW_ID
AND T1.PR_CON_ID = T6.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T7 ON T1.RATE_LST_ID =
T7.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T8 ON T1.ROW_ID =
T8.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ASSET T9 ON T1.ASSET_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T10 ON T1.PR_CON_ID =
T10.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_DOC_AGREE T11 ON T1.AGREEMENT_ID =
T11.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T12 ON T1.PR_EXP_RPT_ID =
T12.ROW_ID
LEFT OUTER JOIN dbo.S_INS_CLAIM T13 ON T1.INSCLM_ID =
T13.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT_SS T14 ON T1.ROW_ID =
T14.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_INT_INSTANCE T15 ON T14.OWN_INST_ID =
T15.ROW_ID
LEFT OUTER JOIN dbo.S_ME_EVT_LS T16 ON T1.ME_EVT_ID =
T16.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T17 ON T1.OPTY_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T18 ON T1.TARGET_OU_ID =
T18.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T19 ON T1.PART_RPR_ID =
T19.ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T20 ON T1.OWNER_POSTN_ID =
T20.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T21 ON T1.SRA_DEFECT_ID =
T21.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T22 ON T1.PROJ_ID = T22.ROW_ID
LEFT OUTER JOIN dbo.S_PROJITEM T23 ON T1.PROJ_ITEM_ID =
T23.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T24 ON T1.SRC_ID = T24.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T25 ON T1.SRA_SR_ID =
T25.ROW_ID
LEFT OUTER JOIN dbo.S_TMPL_PLANITEM T26 ON T1.ASSESS_TMPL_ID
=
T26.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T27 ON T1.PR_TMSHT_LINE_ID
=
T27.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT T28 ON T1.TEMPLATE_ID =
T28.ROW_ID
LEFT OUTER JOIN dbo.S_SUSP_ACT T29 ON T1.ROW_ID =
T29.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_EVT_MAIL T30 ON T1.ROW_ID =
T30.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_SRV_ACT T31 ON T1.ROW_ID =
T31.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ACT_EMP T32 ON T1.OWNER_PER_ID =
T32.EMP_ID AND
T1.ROW_ID = T32.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_PARTY T33 ON T32.EMP_ID = T33.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_FNX T34 ON T32.EMP_ID =
T34.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_USER T35 ON T32.EMP_ID =
T35.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PARTY T36 ON T1.PR_CON_ID = T36.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T37 ON T1.PR_CON_ID =
T37.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_FNX T38 ON T1.PR_CON_ID =
T38.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T39 ON T1.PR_PRDINT_ID =
T39.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PROD_APPL T40 ON T1.PR_PRDINT_ID =
T40.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T41 ON T40.PRDINT_ID =
T41.ROW_ID
WHERE
(
(T1.PAR_EVT_ID = T1.ROW_ID OR T1.PAR_EVT_ID IS NULL OR
T1.PAR_EVT_ID !=
T1.ROW_ID AND T1.OPTY_ID IS NOT NULL OR T1.SUBTYPE_CD =
'General' AND
T1.TODO_CD != 'Marketing eEvent') AND
(T1.APPT_REPT_REPL_CD IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' AND
(T1.OPTY_ID IS NULL OR T17.SECURE_FLG = 'N' OR T1.OPTY_ID IN
(
SELECT SQ1_T2.OPTY_ID
FROM dbo.S_PARTY SQ1_T1
INNER JOIN dbo.S_OPTY_POSTN SQ1_T2
ON SQ1_T2.POSITION_ID = SQ1_T1.ROW_ID
INNER JOIN dbo.S_POSTN SQ1_T3
ON SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT SQ1_T4
ON SQ1_T3.PR_EMP_ID = SQ1_T4.PAR_ROW_ID
WHERE
(SQ1_T4.ROW_ID = '1-23NDP')
)
)
)
AND
(T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
'1-23NDP')) AND
(T1.APPT_START_DT > DATEADD
(SECOND, ROUND (-183 * 86400, 0), '01/15/2004 00:00:00')
OR
T1.TODO_ACTL_END_DT >
DATEADD (SECOND, ROUND (-183 * 86400, 0), '01/15/2004
00:00:00'))

Anyone's got an idea of which index on T1 might help me?

Daniel"Daniel Roy" <danielroy10junk@.hotmail.com> wrote in message
news:3722db.0401190652.41d33a30@.posting.google.com ...
> Hi guys,
> I've been playing with this query for about a week now, and I still
> can't find ways to speed it up. It runs for about 5 minutes. The only
> table (among the 41) with significant amount of data is dbo.S_EVT_ACT,
> which contains about 900,000 records. The query is automatically
> generated by Siebel, and therefore not really modifiable. And hints
> are not really possible. While looking at the exec plan, we can see
> that what takes so long is a clustered index scan of T1. This
> clustered index is defined on a column ROW_ID (a Siebel-generated
> primary key used for every table). I tried to define all kinds of
> indexes in order to bypass this clustered index scan, but no success
> so far ... Here's the query:
> SELECT ...
> FROM
> dbo.S_EVT_ACT T1
> LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T2 ON T1.ROW_ID =
> T2.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_FN_APPR T3 ON T2.AMS_ACT_ID =
> T3.ROW_ID
> LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.PERFRM_BY_OU_ID =
> T4.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.PRI_LST_ID =
> T5.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT T6 ON T1.PR_CON_ID =
> T6.PAR_ROW_ID
> AND T1.PR_CON_ID = T6.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PRI_LST T7 ON T1.RATE_LST_ID =
> T7.ROW_ID
> LEFT OUTER JOIN dbo.S_ACT_PRDINT T8 ON T1.ROW_ID =
> T8.ACTIVITY_ID
> LEFT OUTER JOIN dbo.S_ASSET T9 ON T1.ASSET_ID = T9.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT T10 ON T1.PR_CON_ID =
> T10.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_DOC_AGREE T11 ON T1.AGREEMENT_ID =
> T11.ROW_ID
> LEFT OUTER JOIN dbo.S_EXP_RPT T12 ON T1.PR_EXP_RPT_ID =
> T12.ROW_ID
> LEFT OUTER JOIN dbo.S_INS_CLAIM T13 ON T1.INSCLM_ID =
> T13.ROW_ID
> LEFT OUTER JOIN dbo.S_EVT_ACT_SS T14 ON T1.ROW_ID =
> T14.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_INT_INSTANCE T15 ON T14.OWN_INST_ID =
> T15.ROW_ID
> LEFT OUTER JOIN dbo.S_ME_EVT_LS T16 ON T1.ME_EVT_ID =
> T16.ROW_ID
> LEFT OUTER JOIN dbo.S_OPTY T17 ON T1.OPTY_ID = T17.ROW_ID
> LEFT OUTER JOIN dbo.S_ORG_EXT T18 ON T1.TARGET_OU_ID =
> T18.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PART_RPR T19 ON T1.PART_RPR_ID =
> T19.ROW_ID
> LEFT OUTER JOIN dbo.S_POSTN T20 ON T1.OWNER_POSTN_ID =
> T20.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PROD_DEFECT T21 ON T1.SRA_DEFECT_ID =
> T21.ROW_ID
> LEFT OUTER JOIN dbo.S_PROJ T22 ON T1.PROJ_ID = T22.ROW_ID
> LEFT OUTER JOIN dbo.S_PROJITEM T23 ON T1.PROJ_ITEM_ID =
> T23.ROW_ID
> LEFT OUTER JOIN dbo.S_SRC T24 ON T1.SRC_ID = T24.ROW_ID
> LEFT OUTER JOIN dbo.S_SRV_REQ T25 ON T1.SRA_SR_ID =
> T25.ROW_ID
> LEFT OUTER JOIN dbo.S_TMPL_PLANITEM T26 ON T1.ASSESS_TMPL_ID
> =
> T26.ROW_ID
> LEFT OUTER JOIN dbo.S_TMSHT_LINE T27 ON T1.PR_TMSHT_LINE_ID
> =
> T27.ROW_ID
> LEFT OUTER JOIN dbo.S_EVT_ACT T28 ON T1.TEMPLATE_ID =
> T28.ROW_ID
> LEFT OUTER JOIN dbo.S_SUSP_ACT T29 ON T1.ROW_ID =
> T29.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_EVT_MAIL T30 ON T1.ROW_ID =
> T30.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_SRV_ACT T31 ON T1.ROW_ID =
> T31.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_ACT_EMP T32 ON T1.OWNER_PER_ID =
> T32.EMP_ID AND
> T1.ROW_ID = T32.ACTIVITY_ID
> LEFT OUTER JOIN dbo.S_PARTY T33 ON T32.EMP_ID = T33.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT_FNX T34 ON T32.EMP_ID =
> T34.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_USER T35 ON T32.EMP_ID =
> T35.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PARTY T36 ON T1.PR_CON_ID = T36.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT T37 ON T1.PR_CON_ID =
> T37.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT_FNX T38 ON T1.PR_CON_ID =
> T38.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PROD_INT T39 ON T1.PR_PRDINT_ID =
> T39.ROW_ID
> LEFT OUTER JOIN dbo.S_ACT_PROD_APPL T40 ON T1.PR_PRDINT_ID =
> T40.ROW_ID
> LEFT OUTER JOIN dbo.S_PROD_INT T41 ON T40.PRDINT_ID =
> T41.ROW_ID
> WHERE
> (
> (T1.PAR_EVT_ID = T1.ROW_ID OR T1.PAR_EVT_ID IS NULL OR
> T1.PAR_EVT_ID !=
> T1.ROW_ID AND T1.OPTY_ID IS NOT NULL OR T1.SUBTYPE_CD =
> 'General' AND
> T1.TODO_CD != 'Marketing eEvent') AND
> (T1.APPT_REPT_REPL_CD IS NULL) AND
> (T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' AND
> (T1.OPTY_ID IS NULL OR T17.SECURE_FLG = 'N' OR T1.OPTY_ID IN
> (
> SELECT SQ1_T2.OPTY_ID
> FROM dbo.S_PARTY SQ1_T1
> INNER JOIN dbo.S_OPTY_POSTN SQ1_T2
> ON SQ1_T2.POSITION_ID = SQ1_T1.ROW_ID
> INNER JOIN dbo.S_POSTN SQ1_T3
> ON SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT SQ1_T4
> ON SQ1_T3.PR_EMP_ID = SQ1_T4.PAR_ROW_ID
> WHERE
> (SQ1_T4.ROW_ID = '1-23NDP')
> )
> )
> )
> AND
> (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
> '1-23NDP')) AND
> (T1.APPT_START_DT > DATEADD
> (SECOND, ROUND (-183 * 86400, 0), '01/15/2004 00:00:00')
> OR
> T1.TODO_ACTL_END_DT >
> DATEADD (SECOND, ROUND (-183 * 86400, 0), '01/15/2004
> 00:00:00'))
> Anyone's got an idea of which index on T1 might help me?
> Daniel

According to your query, it looks like you will always have either an index
or table scan of dbo.S_EVT_ACT - it is on the left side of a LEFT JOIN, and
that means that all rows from the table will always be returned. The rows
may be filtered further by the WHERE clause, but MSSQL will require all the
rows as part of the join.

If you had control over the query, you might be able to re-write it with the
filter conditions in the join, not the WHERE clause, or possibly change the
join order to put a smaller table first (although both of those would change
the logic of your query, so might not be straightforward). You could
investigate if Siebel has some facility for changing the join order of a
query, if you can find another join order which is logically equivalent.

Other than that, there probably isn't much you can do, if you don't control
the query itself. One thing that might be possible would be to create
indexed views of common joins, then use the views instead of tables in your
queries. But there are lots of conditions attached to creating indexed views
(including no outer joins), so you would need to do some research and
testing.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> According to your query, it looks like you will always have either an
> index or table scan of dbo.S_EVT_ACT - it is on the left side of a LEFT
> JOIN, and that means that all rows from the table will always be
> returned. The rows may be filtered further by the WHERE clause, but
> MSSQL will require all the rows as part of the join.

I agree that the query is likely to be a hopeless case, but I don't
think you are right on your assement of the left join. Of course SQL Server
can apply the WHERE conditions on S_EVT_ACT, before it does all the
left joins. Consider for instance this query:

select *
from Customers C
left join Orders O ON C.CustomerID = O.CustomerID
where C.City = 'Berlin'

This query performs an Index Seek on C.City.

The problem with Daniel's query is that the WHERE conditions are very
complex. The simplest condition is that T1.APPT_REPT_REPL_CD must be
NULL. And if this condition filters most of the rows, we have an index
candidate here. I'm not holding my breath, though.

Other possibilities would be to create some combined index on some
combinations of columns, or create separate indexes and hope for
index intersection. But it definitely looks like an uphil battle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Simon for your response. Isn't there any way that the
optimizer can decide first to filter the rows from S_EVT_ACT (with a
non-clustered index and a clustered index lookup), and only afterwards
use the resulting rows to address the remaining 40 tables?

Daniel|||Daniel Roy (danielroy10junk@.hotmail.com) writes:
> Thank you Simon for your response. Isn't there any way that the
> optimizer can decide first to filter the rows from S_EVT_ACT (with a
> non-clustered index and a clustered index lookup), and only afterwards
> use the resulting rows to address the remaining 40 tables?

As you might have seen from my post, there is. And in fact it is
likely that what SQL Server will do. However, the conditions are
so complex, that most likely it will have to scan the table anyway.

One of the left joined-tables are special, and that is T17 which
appears in the WHERE clause, so it is possible this has to be
examined first.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Daniel Roy (danielroy10junk@.hotmail.com) writes:
> > Thank you Simon for your response. Isn't there any way that the
> > optimizer can decide first to filter the rows from S_EVT_ACT (with a
> > non-clustered index and a clustered index lookup), and only afterwards
> > use the resulting rows to address the remaining 40 tables?
> As you might have seen from my post, there is. And in fact it is
> likely that what SQL Server will do. However, the conditions are
> so complex, that most likely it will have to scan the table anyway.
> One of the left joined-tables are special, and that is T17 which
> appears in the WHERE clause, so it is possible this has to be
> examined first.

Thanx Erland and Simon for your kind advice. Now that we know that a
(non-clustered) index could possibly be used first before dealing with
the joins, anyone could tell me which index to T1 should be created,
according to the WHERE clause? I agree with you, Erland, when you say
it will be an uphill battle, but I think it's worth a try. Here are
some specific questions which stop me currently:
- can a comparison between 2 columns of the same table be resolved
with a non-clustered index (T1.PAR_EVT_ID = T1.ROW_ID)?
- can an IS NULL condition resolved with a non-clustered index
(T1.PAR_EVT_ID IS NULL)?
- can an inequality between 2 columns of the same table be resolved
with a non-clustered index (T1.PAR_EVT_ID != T1.ROW_ID)?
- can an IS NOT NULL condition resolved with a non-clustered index
(T1.OPTY_ID IS NOT NULL)?
...

As you can see, I'm just looking for guidance on the way that the SQL
Server's optimizer can make use of non-clustered indexes, in order to
help my chances of creating one which would make the clustered index
scan not necessary.

Daniel|||Daniel Roy (danielroy10junk@.hotmail.com) writes:
> Thanx Erland and Simon for your kind advice. Now that we know that a
> (non-clustered) index could possibly be used first before dealing with
> the joins, anyone could tell me which index to T1 should be created,
> according to the WHERE clause? I agree with you, Erland, when you say
> it will be an uphill battle, but I think it's worth a try. Here are
> some specific questions which stop me currently:
> - can a comparison between 2 columns of the same table be resolved
> with a non-clustered index (T1.PAR_EVT_ID = T1.ROW_ID)?
> - can an IS NULL condition resolved with a non-clustered index
> (T1.PAR_EVT_ID IS NULL)?
> - can an inequality between 2 columns of the same table be resolved
> with a non-clustered index (T1.PAR_EVT_ID != T1.ROW_ID)?
> - can an IS NOT NULL condition resolved with a non-clustered index
> (T1.OPTY_ID IS NOT NULL)?
> ...

All sorts of conditions can be resolved by the index, this is not the
problem. The problem is whether the index can be selective enough or not.

Consider these two queries:

select * from Northwind..Orders WHERE CustomerID like 'N%'
select * from Northwind..Orders WHERE CustomerID like 'A%'

If you run them from Query Analyzer after having pressed CTRL-K
to get a tab with the execution plan, you will find that the
first query uses the non-clustered index on CustomerID, while
the second performs a clustered index scan.

Why is this? When SQL Server uses a non-clustered index to retrieve
data, and the query includes columns not present in the index (or
in the clustered index, of which the keys constitute the row pointers
of the non-clustered index) SQL Server must access the data pages.
This is known as "bookmark lookup". That is one access to a page
for each row, so the more rows you find this way, the more likely
you will access the same page more than once. Thus, at some level,
a table scan is cheaper. In the example above there are very few
orders from Customers whose IDs start with N, but there are a bunch
for A.

Now consider this query:

select *
from Northwind..Customers
WHERE CompanyName LIKE 'N%'
OR City = 'Berlin'

There is one index on CompanyName and one City, and if you run
the query in QA, you will find that SQL Server uses both indexes.
Again, if you change 'N%' to 'A%', SQL Server instead opts for a
table scan.

Another important thing to consider is the order of the columns
in a multi-columns index. Say that you have a non-clustered index
on the columns (a, b), and you issue the query:

SELECT * FROM tbl WHERE b = 3

Will SQL Server use the index? Maybe. But it would have to scan the
entire index, since the entries with b = 3 are scattered all over
the index. But if SQL Server has statistics that gives an estimate
that this query will only hit 3 of a million rows, SQL Server will
scan the non-clustered index, since scanning the index require
few page reads than scanning the data pages. (Assuming that the
table has a coupld of more columns.)

In these examples, I have used examples of equality. Of course
conditions with != can also be used, but as you may have realized
by now, they are not very likely to be selective enough.

Note that I say selective, this is not a question about actual
facts, but estimates drawn from the statistics SQL Server have
availble. In these examples, input value has been constants.
When the input value comes from a variable or another column,
SQL Server still can use the distribution, but the plan will be
the same no matter the input value, beause the optimizer does not
that value when it builds the plan.

This should give you some idea. It is possible that you can create
one or more non-clustered indexes, to resolve this, but I would
not hold high hopes.

Maybe I should add one more thing. You could lump about all
columns that appear in the WHERE clause in no particular order.
If there are only one or two more small columns in the table, this
is not likely to have any effect. But there are lot of more columns,
or the remaining columns are big, then you can make some gains
this way. Then SQL Server can evaluate the WHERE clause by scanning
the index, and as I noted above, this is cheaper than scanning
the entire table. (Because there are fewer pages to read.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Daniel Roy (danielroy10junk@.hotmail.com) writes:
> As you can see, I'm just looking for guidance on the way that the SQL
> Server's optimizer can make use of non-clustered indexes, in order to
> help my chances of creating one which would make the clustered index
> scan not necessary.

And, oh, there is one more thing you definitely should try if you have
not already, and that is DBCC DBREINDEX. It is not going to remove
the clustered index scan, but if the table is heavily fragmented, reindexing
can make that scan a lot cheaper.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

indexes urgent please

HI
I created clustered index on 115 million records which runs 6 hours after i
created index now it is running for 16 minutes which is fine.
if i run that query statement(update statement) 1st time it will take 16
minutes and second time it will take 50 minutes
and third time it will take 1hour 30 minutes.this is weired can any one
please let me know what to do
again i am doing rebuilding and updatestats each time.
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
This is probably the wrong group for this, however you need to make sure
that you are not updating a clustered key as this will cause movement and/or
page splits in your clustered index. There may also be lots of
locking/blocking going on depending on how many rows you are updating.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:736fe741cfaeb@.uwe...
> HI
> I created clustered index on 115 million records which runs 6 hours after
> i
> created index now it is running for 16 minutes which is fine.
> if i run that query statement(update statement) 1st time it will take 16
> minutes and second time it will take 50 minutes
> and third time it will take 1hour 30 minutes.this is weired can any one
> please let me know what to do
> again i am doing rebuilding and updatestats each time.
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>