question on reindex

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :

TABLE level scan performed.
- Pages Scanned........................: 169617
- Extents Scanned.......................: 21630
- Extent Switches.......................: 153827
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..............: 45.06%
- Extent Scan Fragmentation ...............: 52.66%
- Avg. Bytes Free per Page................: 5042.5
- Avg. Page Density (full)................: 37.70%

I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.

What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.

The total size of the database is ~150GB.

ThxHave you tried defragmenting by using DBCC INDEXDEFRAG?

Unlike DBCC DBREINDEX (or the index building operation in general),
DBCC INDEXDEFRAG is an online operation. It does not hold locks long
term and thus will not block running queries or updates

Denis the SQL Menace
http://sqlservercode.blogspot.com/
paul wrote:

Quote:

Originally Posted by

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :
>
TABLE level scan performed.
- Pages Scanned........................: 169617
- Extents Scanned.......................: 21630
- Extent Switches.......................: 153827
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..............: 45.06%
- Extent Scan Fragmentation ...............: 52.66%
- Avg. Bytes Free per Page................: 5042.5
- Avg. Page Density (full)................: 37.70%
>
I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.
>
What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.
>
The total size of the database is ~150GB.
>
Thx

Question on parameter.Add for an SQL Insert

I'm trying to update my database table from my dataset.

In the following statement, the ? represents the "size as integer" of the SqlDbType of DateTime and I can't fiqure out what to put there.

command.Parameters.Add("@.ItemDate", SqlDbType.DateTime, ?,"ItemDate")

In the following statement I understand the "50" since it is the size of the field, but I don't understand what to do with the DateTime.

command.Parameters.Add("@.ItemText", SqlDbType.NVarChar, 50, "ItemText")

Thank you for your help

This chart might help:

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Buck Woody

Question on parameter.Add for an SQL Insert

I'm trying to update my database table from my dataset.

In the following statement, the ? represents the "size as integer" of the SqlDbType of DateTime and I can't fiqure out what to put there.

command.Parameters.Add("@.ItemDate", SqlDbType.DateTime, ?,"ItemDate")

In the following statement I understand the "50" since it is the size of the field, but I don't understand what to do with the DateTime.

command.Parameters.Add("@.ItemText", SqlDbType.NVarChar, 50, "ItemText")

Thank you for your help

This chart might help:

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Buck Woody

Question on Inserting to a different server

What would be the into statement when I am trying to do an insert into another database from SQL
basically want to do something like this but its not working quite right
insert into DatabaseName.TableName
Any help would be greatly appreciated...Thanksinsert server.db.dbo.table values(...)

server - linked server. See BOL fro details.|||You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.

To transfer data between the databases you can take help of DTS which is easy and manageble.

Question on encryption/ keys/ certificates/ etc.

SQL2K5
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
the[vbcol=seagreen]
settings[vbcol=seagreen]
pretty[vbcol=seagreen]
>

Question on encryption/ keys/ certificates/ etc.

SQL2K5
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> > SQL2K5
> > SP1
> >
> > I was able to sucessfully create a Master Key, create a Certificate,
> > create
> > a Symmetric Key and assign it to the Certificate, insert encrypted data,
> > and
> > then decrypt/ read that data. Pretty cool stuff. But Im reading up on
the
> > topic and Im under the impression that I should also have needed too:
> >
> > Set Force Encryption to Yes.
> > Configure the DB engine to use a Cert.
> > Reboot the box.
> >
> > But I didn't have to do any of that. I just went and verified the
settings
> > and thats not how SQL is configured. Im obviosuly missing something
pretty
> > big here, can someone please assist?
> >
> > TIA, ChrisR
> >
> >
>