Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
Erik
Hi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created index
es
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexe
s
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automated Batch Job Over Numerous Data Files, How?

Hello, Everyone: Greetings!

I am new to Sql Server [Express version] and am not even sure I'm making the right choice. So here I am, seeking advice.

My database needs are nothing sophisticated. They just involve:
(a) create tens of thousands of separate data files each under a unique file name of up to 8 characters, with file names read in from a pre-determined file name list.
(b) store/insert VOLUMINOUS numerical data into each of the data files, with the data indexed by date&time, plus maybe one or two additional character or string fields.
(c) for each data file, retrieve a subset of its data, perform extensive numerical calculations, and then store the results in one or more separate corresponding files, e.g. if a file name in (b) is F12345, (c) creates F12345R1, F12345R2, F12345R3, etc. which stores different sets of calculated results.

Thus, this is purely a console application, doing a batch job, and requiring no graphical user interface. Both automation and speed are important here, due to the large number of data files that must be created and/or updated, and the very extensive numerical calculations on the data.

The goal is to automate the daily or weekly creation of each of the tens of thousands of Sql Server database files, insert fresh data (read in from a fresh ASCII file) into each file, numerically process the data and then store the results in one or more separate, corresponding result data files, with all the steps automated and without need for GUI. Once coding is done, the entire data processing session is expected to run for many hours, or even days, in an automated manner, and without human intervention.

What would be the most efficient way of doing this under Visual Basic Express (which is what I'm learning to use) by directly calling Sql Server Express without having to go through GUI to create database files? What is the proper interface utility or library to use to enable direct database function calls without the need to learn SQL language? Is Visual Basic and/or Sql Server even good choices for what I want to do? I want to be able to call the basic, simple database functions directly and simply from program code in a non-GUI, non-interactive manner for the tens of thousands of separate data files that will be used.

I really miss the good old days when one can do a straightforward batch job via a console application, with simple, direct calls to create new data files, insert and index fresh data, retrieve any subset of data to do extensive calculations, create new files to store the results, etc. all under automated program control and iterating through unlimited number of data files, until the job is finished, all without human intervention during processing.

Or am I missing something because all this can still be done simply and easily under VB and Sql Server? I've several books here about Visual Basic 2005 and Visual Basic 2005 Express, all showing how to create a database via a GUI utility. That's fine if one needs to create just one or two databases, but not hundreds, or even tens of thousands (as in my case) of them on the fly.

So, I am looking for the simplest and most direct database interface that will allow me to do the above under VB program code alone, and easily. For something as simple as I have described above, I don't think I should have to learn the SQL language or manually create each database file.

As you can see, I just want to get some heavy duty numerical processing job done over tens of thousands of data files as simply and efficiently as possible, and with as little fanciful detour as possible. So, ironically, I am trying to use Visual Basic without being cluttered by having to learn its "Visual" aspects, yet GUI is what most VB books devote to or emphasize heavily. Similarly, I would much rather use simple, "lean and mean", direct database function calls than having to learn a new vocabulary of "English-like" SQL language.
Yes, I'm not used to this tedious detour of learning the GUI aspect of VB, or learning the Structured Query Language of Sql Server, just to try to do something simple that I need to do in batch mode via a console application.

Are there any good books or other helpful URLs that will help a guy like me? Am I even using the wrong language and the wrong database to do what I want to do? What are the better alternatives, if any? Any advice, experience and pointers on any of the above issues raised would be very much appreciated. Thank you!

Regards,
ConsoleApp

You've asked a fairly large, fairly non-trivial question...

SQL Server 2005 Express Edition is a good place to start, as you've surmised. Depending on your background and expertise, Visual C# or Visual Basic from Visual Studio 2005 would be a great programming environment. You'll be able to create a new database and add tables, as well as use the database project for managing the TSQL scripts that can be used to create these database objects on the fly when your application runs. Depending on your indexing vs. partitioning decision, you may only ever need to create the database and tables one time.

In both C# and Visual Basic, a console app that does what you're asking it to do should be possible. My only concern (follow up in one of the Visual Studio forums for an autoritative answer and guide to this) is the ability to use some of the great data designer tools in a UI-less application -- the designers and associated runtime controls are targeted toward a rich client application and thus assume a windowed application. For that reason, you may find that prototyping your application as a Windows Forms application may be easier to start with and then transition to a non-UI application at a later date (note that a windowed app can still serve as the container for a batch processing type app such as this...what you're asking for is an application that doesn't display UI that blocks the progress of the job).

As to some of the other aspects of your application, you will more likely want to create lots of tables, not databases. There is a very rough correlation between databases and directories and between tables and files...that general mindset will probably help you transition your mental model. Also, look at the justification behind partitioning your data into many, many files...in a database, you can use indexing to differentiate these groupings of data (e.g. SELECT * FROM [Data] WHERE [SensorID]=4). For extremely large data sets, there may still be some value in partitioning, but I suspect that this is a tertiary consideration at this point.

And finally, your processed data could be stored in additional tables much as your input data was. On these tables you could then do additional analysis/reporting...

Are you parsing out non-contiguous subsets of data (e.g. [1-10], [11-20], [21-30] or is it [1,2,4,10,12], [2, 20,21,30,33], etc) on a regular basis? Or do you process data files in their entirety?

Automated Batch Job Over Numerous Data Files, How?

Hello, Everyone: Greetings!

I am new to Sql Server [Express version] and am not even sure I'm making the right choice. So here I am, seeking advice.

My database needs are nothing sophisticated. They just involve:
(a) create tens of thousands of separate data files each under a unique file name of up to 8 characters, with file names read in from a pre-determined file name list.
(b) store/insert VOLUMINOUS numerical data into each of the data files, with the data indexed by date&time, plus maybe one or two additional character or string fields.
(c) for each data file, retrieve a subset of its data, perform extensive numerical calculations, and then store the results in one or more separate corresponding files, e.g. if a file name in (b) is F12345, (c) creates F12345R1, F12345R2, F12345R3, etc. which stores different sets of calculated results.

Thus, this is purely a console application, doing a batch job, and requiring no graphical user interface. Both automation and speed are important here, due to the large number of data files that must be created and/or updated, and the very extensive numerical calculations on the data.

The goal is to automate the daily or weekly creation of each of the tens of thousands of Sql Server database files, insert fresh data (read in from a fresh ASCII file) into each file, numerically process the data and then store the results in one or more separate, corresponding result data files, with all the steps automated and without need for GUI. Once coding is done, the entire data processing session is expected to run for many hours, or even days, in an automated manner, and without human intervention.

What would be the most efficient way of doing this under Visual Basic Express (which is what I'm learning to use) by directly calling Sql Server Express without having to go through GUI to create database files? What is the proper interface utility or library to use to enable direct database function calls without the need to learn SQL language? Is Visual Basic and/or Sql Server even good choices for what I want to do? I want to be able to call the basic, simple database functions directly and simply from program code in a non-GUI, non-interactive manner for the tens of thousands of separate data files that will be used.

I really miss the good old days when one can do a straightforward batch job via a console application, with simple, direct calls to create new data files, insert and index fresh data, retrieve any subset of data to do extensive calculations, create new files to store the results, etc. all under automated program control and iterating through unlimited number of data files, until the job is finished, all without human intervention during processing.

Or am I missing something because all this can still be done simply and easily under VB and Sql Server? I've several books here about Visual Basic 2005 and Visual Basic 2005 Express, all showing how to create a database via a GUI utility. That's fine if one needs to create just one or two databases, but not hundreds, or even tens of thousands (as in my case) of them on the fly.

So, I am looking for the simplest and most direct database interface that will allow me to do the above under VB program code alone, and easily. For something as simple as I have described above, I don't think I should have to learn the SQL language or manually create each database file.

As you can see, I just want to get some heavy duty numerical processing job done over tens of thousands of data files as simply and efficiently as possible, and with as little fanciful detour as possible. So, ironically, I am trying to use Visual Basic without being cluttered by having to learn its "Visual" aspects, yet GUI is what most VB books devote to or emphasize heavily. Similarly, I would much rather use simple, "lean and mean", direct database function calls than having to learn a new vocabulary of "English-like" SQL language.
Yes, I'm not used to this tedious detour of learning the GUI aspect of VB, or learning the Structured Query Language of Sql Server, just to try to do something simple that I need to do in batch mode via a console application.

Are there any good books or other helpful URLs that will help a guy like me? Am I even using the wrong language and the wrong database to do what I want to do? What are the better alternatives, if any? Any advice, experience and pointers on any of the above issues raised would be very much appreciated. Thank you!

Regards,
ConsoleApp

You've asked a fairly large, fairly non-trivial question...

SQL Server 2005 Express Edition is a good place to start, as you've surmised. Depending on your background and expertise, Visual C# or Visual Basic from Visual Studio 2005 would be a great programming environment. You'll be able to create a new database and add tables, as well as use the database project for managing the TSQL scripts that can be used to create these database objects on the fly when your application runs. Depending on your indexing vs. partitioning decision, you may only ever need to create the database and tables one time.

In both C# and Visual Basic, a console app that does what you're asking it to do should be possible. My only concern (follow up in one of the Visual Studio forums for an autoritative answer and guide to this) is the ability to use some of the great data designer tools in a UI-less application -- the designers and associated runtime controls are targeted toward a rich client application and thus assume a windowed application. For that reason, you may find that prototyping your application as a Windows Forms application may be easier to start with and then transition to a non-UI application at a later date (note that a windowed app can still serve as the container for a batch processing type app such as this...what you're asking for is an application that doesn't display UI that blocks the progress of the job).

As to some of the other aspects of your application, you will more likely want to create lots of tables, not databases. There is a very rough correlation between databases and directories and between tables and files...that general mindset will probably help you transition your mental model. Also, look at the justification behind partitioning your data into many, many files...in a database, you can use indexing to differentiate these groupings of data (e.g. SELECT * FROM [Data] WHERE [SensorID]=4). For extremely large data sets, there may still be some value in partitioning, but I suspect that this is a tertiary consideration at this point.

And finally, your processed data could be stored in additional tables much as your input data was. On these tables you could then do additional analysis/reporting...

Are you parsing out non-contiguous subsets of data (e.g. [1-10], [11-20], [21-30] or is it [1,2,4,10,12], [2, 20,21,30,33], etc) on a regular basis? Or do you process data files in their entirety?

Automate to get version number for a list of SQL Instances

We have a bunch of SQL Server instances in our domain. Some of them
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>

Auto identity field with check constraint

We are testing with the CTP Sept. version of SQL 2005 in hopes of
moving to the released version of SQL 2005. We have a table that has
an autoidentity range, with "Not For Replication" set to yes. (Is in a
merge-snapshot publication.) In SQL 2000, you used to be able to
uncheck the constraint "Enforce For inserts and Updates". Does anyone
know why you cannot do this in SQL 2005? This identity field
(location_ID) ss needed so that my users can add their own address
information concerning a prospect company, but I also need to insert
"real" location_IDs from existing companies. I try doing a SET
IDENTITY_INSERT ON, and try to insert but receive the below error:
It conflicted with an identity range check constraint
Does anyone know how I can work around this? (Is it resolved in the
released version of SQL 2005?)
Thanks,
Amy
Have you tried altering the check constraint directly: ALTER TABLE
cnst_example NOCHECK CONSTRAINT salary_cap. Resetting the current identity,
then reenabling the check constraint?
Rgds,
Paul Ibison
|||Paul Ibison wrote:
> Have you tried altering the check constraint directly: ALTER TABLE
> cnst_example NOCHECK CONSTRAINT salary_cap. Resetting the current identity,
> then reenabling the check constraint?
> Rgds,
> Paul Ibison
Paul, That works.. Thanks.. Do you know of any issues with doing this
if a user happens to replicate while this is happening? I insert data
in the morning, so chances are very slim.
Thanks for your help!
Amy
|||It won't change the value of the identity attribute so things can't go wrong
that way. the potential issue with what you have is that the automatic range
management algorithm doesn't take account of your extra values when
allocating a new range once this one has been completed. Also I'd be
concerned that a subscriber may have this range itself and cause a PK
overlap.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
We won't have a primary key problem, because the initial identity
number was set way above any number I will be inputting into the table.
Thanks so much for your help!
Amy