Database Size and SP4

As far as I know, the database size contains both Index and actual data. Is
there any command to find out the exact space occupied used by Index and
Data ? The SQL Server 2000 Database is of 5GB allocated with data (I think
it is data + Index) of 1.5GB.
Some fellow has mentioned that applying SP4 will get a better management of
memory. Is it correct ?
ThanksRobert
sp_helpdb 'pubs'
use pubs
go
sp_spaceused
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
> As far as I know, the database size contains both Index and actual data.
> Is there any command to find out the exact space occupied used by Index
> and Data ? The SQL Server 2000 Database is of 5GB allocated with data (I
> think it is data + Index) of 1.5GB.
> Some fellow has mentioned that applying SP4 will get a better management
> of memory. Is it correct ?
> Thanks
>|||Dear Uri,
Thank you for your advice.
s there any general rule of thumb about the ratio between Index and Data ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
> Robert
> sp_helpdb 'pubs'
> use pubs
> go
> sp_spaceused
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual data.
>> Is there any command to find out the exact space occupied used by Index
>> and Data ? The SQL Server 2000 Database is of 5GB allocated with data (I
>> think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better management
>> of memory. Is it correct ?
>> Thanks
>|||Robert
Keep your indexes narrow as you can.
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for your advice.
> s there any general rule of thumb about the ratio between Index and Data ?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual data.
>> Is there any command to find out the exact space occupied used by Index
>> and Data ? The SQL Server 2000 Database is of 5GB allocated with data
>> (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better management
>> of memory. Is it correct ?
>> Thanks
>>
>|||Dear Uri,
Can you elaborate what does index narrow means ?
Thanking you in anticipation.
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
> Robert
> Keep your indexes narrow as you can.
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for your advice.
>> s there any general rule of thumb about the ratio between Index and Data
>> ?
>> Thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual
>> data. Is there any command to find out the exact space occupied used by
>> Index and Data ? The SQL Server 2000 Database is of 5GB allocated with
>> data (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better
>> management of memory. Is it correct ?
>> Thanks
>>
>>
>|||Robert
Short, that means to create an index on columns like INT (4 bytes....) and
not on VARCHAR(8000).....
http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Can you elaborate what does index narrow means ?
> Thanking you in anticipation.
> Rob
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> Keep your indexes narrow as you can.
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for your advice.
>> s there any general rule of thumb about the ratio between Index and Data
>> ?
>> Thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual
>> data. Is there any command to find out the exact space occupied used
>> by Index and Data ? The SQL Server 2000 Database is of 5GB allocated
>> with data (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better
>> management of memory. Is it correct ?
>> Thanks
>>
>>
>>
>|||Thank you for your advice
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:emhSBlOoGHA.1668@.TK2MSFTNGP05.phx.gbl...
> Robert
> Short, that means to create an index on columns like INT (4 bytes....)
> and not on VARCHAR(8000).....
> http://www.sql-server-performance.com/clustered_indexes.asp
> http://www.sql-server-performance.com/nonclustered_indexes.asp
>
>
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Can you elaborate what does index narrow means ?
>> Thanking you in anticipation.
>> Rob
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> Keep your indexes narrow as you can.
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for your advice.
>> s there any general rule of thumb about the ratio between Index and
>> Data ?
>> Thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual
>> data. Is there any command to find out the exact space occupied used
>> by Index and Data ? The SQL Server 2000 Database is of 5GB allocated
>> with data (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better
>> management of memory. Is it correct ?
>> Thanks
>>
>>
>>
>>
>

Database Size and SP4

As far as I know, the database size contains both Index and actual data. Is
there any command to find out the exact space occupied used by Index and
Data ? The SQL Server 2000 Database is of 5GB allocated with data (I think
it is data + Index) of 1.5GB.
Some fellow has mentioned that applying SP4 will get a better management of
memory. Is it correct ?
ThanksRobert
sp_helpdb 'pubs'
use pubs
go
sp_spaceused
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
> As far as I know, the database size contains both Index and actual data.
> Is there any command to find out the exact space occupied used by Index
> and Data ? The SQL Server 2000 Database is of 5GB allocated with data (I
> think it is data + Index) of 1.5GB.
> Some fellow has mentioned that applying SP4 will get a better management
> of memory. Is it correct ?
> Thanks
>|||Dear Uri,
Thank you for your advice.
s there any general rule of thumb about the ratio between Index and Data ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
> Robert
> sp_helpdb 'pubs'
> use pubs
> go
> sp_spaceused
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>|||Robert
Keep your indexes narrow as you can.
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for your advice.
> s there any general rule of thumb about the ratio between Index and Data ?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>|||Dear Uri,
Can you elaborate what does index narrow means ?
Thanking you in anticipation.
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
> Robert
> Keep your indexes narrow as you can.
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>|||Robert
Short, that means to create an index on columns like INT (4 bytes....) and
not on VARCHAR(8000).....
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Can you elaborate what does index narrow means ?
> Thanking you in anticipation.
> Rob
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
>|||Thank you for your advice
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:emhSBlOoGHA.1668@.TK2MSFTNGP05.phx.gbl...
> Robert
> Short, that means to create an index on columns like INT (4 bytes....)
> and not on VARCHAR(8000).....
> http://www.sql-server-performance.c...red_indexes.asp
> http://www.sql-server-performance.c...red_indexes.asp
>
>
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
>

Database Restore

If I make a database backup with the following command
backup database SkatingResults to SkatingResultsBackUp with skip, format,
DESCRIPTION='Skating Results Backup', MEDIADESCRIPTION='Disk File'
GO
I am wanting to restore that database backup onto another computer.
Since the computer that I will be going will not be restoring to the same
locatin, I must use the "move" option of the restore.
Here is where I am confuse, what does my restore statement look like. The
example I saw in BOL says I should "Determine the number and names of the
files in the backup" by executing "RESTORE FILELISTONLY". The example of
the command is as follows:
RESTORE FILELISTONLY
FROM MyNwind_1
What is "MyNwind_1" - is this the "backup devise". The Backup Devise
points the computer to the actual backup file to work with?
Would I have use a statement like the following to establish the "Backup
Devise"?
exec sp_addumpdevice 'disk', 'SkatingResultsBackUp', 'H:\Temp
\SkatingResultsBackup.dat'
Then I would place my backup file in this location and perform the "RESTORE
FILELISTONLY" and "RESTORE DATABASE". There is no need for me to restor
the transaction log if my database is empty?
Thanks inadvance for your assistance!!!!!!!!!!Jim
I am not sure what your back statement creates, since I have never used that syntax. I usually use this
BACKUP DATABASE DB1TO Disk='C:\MSSQL\Backup\DB1.bck
Assuming it creates a disk file called SkatingResultsBackUp.bck or SkatingResultsBackUp.bak you can issue this command
RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\BACKUP\SkatingResultsBackUp.bak
replacing the path with your
The output of this command basically list what is in the sysfiles table
Assuming that you database consist of two files, 1 data and 1 log
Restore the database with this command
RESTORE DATABASE SkatingResults FROM DISK = 'D:\MSSQL7\BACKUP\SkatingResultsBackUp.bck
WITH MOVE 'SkatingResults_dat' TO 'D:\MSSQL\Data\SkatingResults_Data.mdf'
MOVE 'SkatingResults_log' TO 'D:\MSSQL\Data\SkatingResults_log.ldf
again replacing the paths with your
HTH
Norman

database performance management

Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.

please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.

As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.

It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.

At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.