Hello. I am wondering how to effectively reduce the size of my database. After viewing the individual table sizes, I have come to realize that nearly 99% of the database's size is due to images. I am told that too much binary data is not good. How can I go about reducing the size of my database (possibly the images themselves)? I'd appreciate any help.store the path and filename instead of the image?
Do you use READTEXT WRITETEXT alot?
what's the size of a average image?|||The average size is around 680k. I do not use readtext and writetext a lot. How do i go about storing the path instead of the actual image?|||Someone suggested a UNC path to the images. What is this?|||UNC = Universal Naming Convention
\\servername\sharename\pathname\filename.img
so you can place the images anywhere|||is there an easy way to do this if I have around 360 images that need to be moved? What do i put in the table's data field|||UNC=\\SERVER\SHARE\FILENAME.EXT
There are kudos of reasons to store images in the database itself. It all depends on the app itself, and what is actually stored in those images. I prefer to use image and (n)text fields, because that's what a database is for. And if there is anything that is associated with the business through your app, - its place is in the database. The question comes how this info is being used. For example, in healthcare industry it becomes more and more practical to do high-speed scanning of claims. Scanned images are burned onto media of choice. Some organizations are also storing those images into database (TIFF format), others go even further and apply OCR technology to avoid data entry process. Key elements of the claim image are stored in related tables to meet search needs. The only time the image field is accessed is when there is a need to view the original claim. Will it make sense to store a path to a TIFF file instead of the file itself? Does the database have any control over OS environment? NO!!! In other cases Full-Text Search capability is used if the type of file being stored meets the parsing and filtering capabilities of the search engine (Word documents for example.) Whoever told you that "too much binary data is not good" is operating under unqualified assumptions. You need to look into the nature of the business usage of your app and determine whether it does or does not make sense to rely on database for safe-keeping of your images (I think the answer is pretty clear, hey?!)|||And the debate starts again...
If you have significant volume...I'd say it's an issue...
Hell they went out and bought kodak imagining platter to handle everything...it's massive...
no way a db could handle this volume...
There's also the pain of reaind and writing in chunks (how do you update a chunk again? Is it the whole thing?)
How big are the images?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36026|||He told you, - 680K|||My problem is that the database itself is far too large, and it is 99% due to these images. They are simply photos of employees and there are around 360 of them. We use them on a web site where once a name is typed in, info about that person is displayed; background info, history with the company, sales numbers, the photo, etc. How do i go about creating this UNC to the images? Where do you recommend i save this pictures? I appreciate any help.|||So, are we talking about less than 250MB worth of images? If this size is taken by 1% of your structure, then your database is less than 500MB all together. Why are you concerned about this? Why are you calling it "a problem?"|||What is far too large?
If the pix are 1 mb you only be saving 360 mb
That's not big
Saturday, February 25, 2012
reducing compilations per second?
Does anybody have a good list or know of a good website that shows various
things you can do to reduce the compilations per second? I'm running SQL
Server 2000 and I'm getting anywhere from 114 to 200 compilations per
second, and obviously my CPU is pegged when it hits that higher end. What I
do know that is supposed to help:
1. upgrade to 2005
2. increase physical memory
3. increase minimum server memory setting
4. use a single database instead of several identical ones.
5. possibly change the code to use the full database path including the
owner for table names
Anything else?
thanks,
Coryhttp://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx
SQL compilations of Transact-SQL code is a normal part of SQL Server's
operation. But because compilations chew up CPU and other resources, SQL
attempts to reuse as many execution plans in cache as possible (execution
plans are created when compilations occur). The more execution plans are
reused, the less overhead there is on the server, and the faster overall
performance there is.
To find out how many compilations SQL Server is doing, you can monitor the
SQLServer: SQL Statistics: SQL Compilations/Sec counter. As you would
expect, this measures how many compilations are performed by SQL Server per
second.
Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur. In some cases, you have
the power to reduce the number of unnecessary compilations. See this page
for tips on how to do this.
http://www.sql-server-performance.com/tips/stored_procedures.asp
If you find that your server is performing over 100 compilations per second,
you should take the time to investigate if the cause of this is something
that you can control. Too many compilations will hurt your SQL Server's
performance. [7.0, 2000] Updated 9-4-2006
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||correct link
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||Also, did you run a trace and see where the compilations are coming from?
What queries are being executed and by whom?
--
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||See in-line
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
This can help as it can compile at the statement level and if you have
lots of adhoc code (which it sounds like you do) you can use the FORCE
parameterization option.
> 2. increase physical memory
This may or may not help. If the code is simply not reusable this may
make matters worse.
> 3. increase minimum server memory setting
This will not help unless you have other apps on the same server. In
that case think about removing them.
> 4. use a single database instead of several identical ones.
Not a good way to go to fix compilation issues. Optimize the code first.
> 5. possibly change the code to use the full database path including the
> owner for table names
That is not the full path. FULL would be DB.Owner.Object. But you are
on the right track. ALWAYS owner qualify your objects.
> Anything else?
Yes, optimize the code so the queries can be reused. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>
> thanks,
> Cory
>
>|||1) Are you running stored procs or ADO/ADONET code? Some quick tips for
sproc compilations:
a) reduce temp table usage (table variables maybe, or combine statements to
eliminate temp storage needs altogether)
b) create all temp objects at top of sproc
c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
Reducing compilations from ADO/ADONET code usually requires rewriting the
code to use more set based logic. As Andy Kelly said, on 2005 you can use
the FORCED PARAMETERIZATION database setting. I have a client that saw a
30-40% throughput improvement on their bad ADO code using that setting.
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||We are running nearly 100% ColdFusion code. We aren't using temp tables
even in the few stored procs that we have. We are currently rewriting the
most popular apps on our sites to use parameters on everything. We did look
into the cache on the server using some of the system stored procedures and
we realized that the ones that were parameterized are getting used over 200
times, while the ad-hoc queries are compiled separately with every hit, and
only have one hit on them.
Also, I posted this elsewhere:
One thing that we are still very confused about is how the initial lookup
actually works. We're mainly wondering if two similar queries that work
identically can share the same execution plan in the cache. For example, if
you ran this:
<cfquery>
Select field from table where ID = <CFQUERYPARAM value="1">
</cfquery>
Then on a different page that you wrote before you knew all about code reuse
in CFC's and stuff, you ran this:
<cfquery>
SELECT field
FROM table
WHERE id = <CFQUERYPARAM value="1">
</cfquery>
Same thing, but formatted differently. Will they both have to be compiled
into two different execution plans, or is SQL Server smart enough to use the
same plan in the cache? Is it a simple text lookup, or are formatting
characters stripped and it reduced down to bytecode or something? I have no
idea.
Thanks,
Cory
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13fkrvpprbubr9a@.corp.supernews.com...
> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for
> sproc compilations:
> a) reduce temp table usage (table variables maybe, or combine statements
> to eliminate temp storage needs altogether)
> b) create all temp objects at top of sproc
> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
> Reducing compilations from ADO/ADONET code usually requires rewriting the
> code to use more set based logic. As Andy Kelly said, on 2005 you can use
> the FORCED PARAMETERIZATION database setting. I have a client that saw a
> 30-40% throughput improvement on their bad ADO code using that setting.
>
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows
>> various things you can do to reduce the compilations per second? I'm
>> running SQL Server 2000 and I'm getting anywhere from 114 to 200
>> compilations per second, and obviously my CPU is pegged when it hits that
>> higher end. What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the
>> owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>|||If you don't parameterize your queries (which can be identified using a profiler trace), then SQL
Server does the lookup using a checksum of the whole query string. i.e., it is
"everything"-sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OqRkKMRAIHA.4164@.TK2MSFTNGP06.phx.gbl...
> We are running nearly 100% ColdFusion code. We aren't using temp tables even in the few stored
> procs that we have. We are currently rewriting the most popular apps on our sites to use
> parameters on everything. We did look into the cache on the server using some of the system
> stored procedures and we realized that the ones that were parameterized are getting used over 200
> times, while the ad-hoc queries are compiled separately with every hit, and only have one hit on
> them.
>
> Also, I posted this elsewhere:
> One thing that we are still very confused about is how the initial lookup actually works. We're
> mainly wondering if two similar queries that work identically can share the same execution plan in
> the cache. For example, if you ran this:
>
> <cfquery>
> Select field from table where ID = <CFQUERYPARAM value="1">
> </cfquery>
>
> Then on a different page that you wrote before you knew all about code reuse in CFC's and stuff,
> you ran this:
>
>
> <cfquery>
> SELECT field
> FROM table
> WHERE id = <CFQUERYPARAM value="1">
> </cfquery>
>
>
>
> Same thing, but formatted differently. Will they both have to be compiled into two different
> execution plans, or is SQL Server smart enough to use the same plan in the cache? Is it a simple
> text lookup, or are formatting characters stripped and it reduced down to bytecode or something?
> I have no idea.
>
>
> Thanks,
> Cory
>
>
>
>
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13fkrvpprbubr9a@.corp.supernews.com...
>> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for sproc compilations:
>> a) reduce temp table usage (table variables maybe, or combine statements to eliminate temp
>> storage needs altogether)
>> b) create all temp objects at top of sproc
>> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
>> Reducing compilations from ADO/ADONET code usually requires rewriting the code to use more set
>> based logic. As Andy Kelly said, on 2005 you can use the FORCED PARAMETERIZATION database
>> setting. I have a client that saw a 30-40% throughput improvement on their bad ADO code using
>> that setting.
>>
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows various things you can do to
>> reduce the compilations per second? I'm running SQL Server 2000 and I'm getting anywhere from
>> 114 to 200 compilations per second, and obviously my CPU is pegged when it hits that higher end.
>> What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>>
>|||Have a look at these to see how this works and how to address it.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
http://support.microsoft.com/kb/243588
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OqRkKMRAIHA.4164@.TK2MSFTNGP06.phx.gbl...
> We are running nearly 100% ColdFusion code. We aren't using temp tables
> even in the few stored procs that we have. We are currently rewriting the
> most popular apps on our sites to use parameters on everything. We did
> look into the cache on the server using some of the system stored
> procedures and we realized that the ones that were parameterized are
> getting used over 200 times, while the ad-hoc queries are compiled
> separately with every hit, and only have one hit on them.
>
> Also, I posted this elsewhere:
> One thing that we are still very confused about is how the initial lookup
> actually works. We're mainly wondering if two similar queries that work
> identically can share the same execution plan in the cache. For example,
> if you ran this:
>
> <cfquery>
> Select field from table where ID = <CFQUERYPARAM value="1">
> </cfquery>
>
> Then on a different page that you wrote before you knew all about code
> reuse in CFC's and stuff, you ran this:
>
>
> <cfquery>
> SELECT field
> FROM table
> WHERE id = <CFQUERYPARAM value="1">
> </cfquery>
>
>
>
> Same thing, but formatted differently. Will they both have to be compiled
> into two different execution plans, or is SQL Server smart enough to use
> the same plan in the cache? Is it a simple text lookup, or are formatting
> characters stripped and it reduced down to bytecode or something? I have
> no idea.
>
>
> Thanks,
> Cory
>
>
>
>
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13fkrvpprbubr9a@.corp.supernews.com...
>> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for
>> sproc compilations:
>> a) reduce temp table usage (table variables maybe, or combine statements
>> to eliminate temp storage needs altogether)
>> b) create all temp objects at top of sproc
>> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
>> Reducing compilations from ADO/ADONET code usually requires rewriting the
>> code to use more set based logic. As Andy Kelly said, on 2005 you can
>> use the FORCED PARAMETERIZATION database setting. I have a client that
>> saw a 30-40% throughput improvement on their bad ADO code using that
>> setting.
>>
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows
>> various things you can do to reduce the compilations per second? I'm
>> running SQL Server 2000 and I'm getting anywhere from 114 to 200
>> compilations per second, and obviously my CPU is pegged when it hits
>> that higher end. What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the
>> owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>>
>
things you can do to reduce the compilations per second? I'm running SQL
Server 2000 and I'm getting anywhere from 114 to 200 compilations per
second, and obviously my CPU is pegged when it hits that higher end. What I
do know that is supposed to help:
1. upgrade to 2005
2. increase physical memory
3. increase minimum server memory setting
4. use a single database instead of several identical ones.
5. possibly change the code to use the full database path including the
owner for table names
Anything else?
thanks,
Coryhttp://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx
SQL compilations of Transact-SQL code is a normal part of SQL Server's
operation. But because compilations chew up CPU and other resources, SQL
attempts to reuse as many execution plans in cache as possible (execution
plans are created when compilations occur). The more execution plans are
reused, the less overhead there is on the server, and the faster overall
performance there is.
To find out how many compilations SQL Server is doing, you can monitor the
SQLServer: SQL Statistics: SQL Compilations/Sec counter. As you would
expect, this measures how many compilations are performed by SQL Server per
second.
Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur. In some cases, you have
the power to reduce the number of unnecessary compilations. See this page
for tips on how to do this.
http://www.sql-server-performance.com/tips/stored_procedures.asp
If you find that your server is performing over 100 compilations per second,
you should take the time to investigate if the cause of this is something
that you can control. Too many compilations will hurt your SQL Server's
performance. [7.0, 2000] Updated 9-4-2006
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||correct link
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||Also, did you run a trace and see where the compilations are coming from?
What queries are being executed and by whom?
--
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||See in-line
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
This can help as it can compile at the statement level and if you have
lots of adhoc code (which it sounds like you do) you can use the FORCE
parameterization option.
> 2. increase physical memory
This may or may not help. If the code is simply not reusable this may
make matters worse.
> 3. increase minimum server memory setting
This will not help unless you have other apps on the same server. In
that case think about removing them.
> 4. use a single database instead of several identical ones.
Not a good way to go to fix compilation issues. Optimize the code first.
> 5. possibly change the code to use the full database path including the
> owner for table names
That is not the full path. FULL would be DB.Owner.Object. But you are
on the right track. ALWAYS owner qualify your objects.
> Anything else?
Yes, optimize the code so the queries can be reused. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>
> thanks,
> Cory
>
>|||1) Are you running stored procs or ADO/ADONET code? Some quick tips for
sproc compilations:
a) reduce temp table usage (table variables maybe, or combine statements to
eliminate temp storage needs altogether)
b) create all temp objects at top of sproc
c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
Reducing compilations from ADO/ADONET code usually requires rewriting the
code to use more set based logic. As Andy Kelly said, on 2005 you can use
the FORCED PARAMETERIZATION database setting. I have a client that saw a
30-40% throughput improvement on their bad ADO code using that setting.
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||We are running nearly 100% ColdFusion code. We aren't using temp tables
even in the few stored procs that we have. We are currently rewriting the
most popular apps on our sites to use parameters on everything. We did look
into the cache on the server using some of the system stored procedures and
we realized that the ones that were parameterized are getting used over 200
times, while the ad-hoc queries are compiled separately with every hit, and
only have one hit on them.
Also, I posted this elsewhere:
One thing that we are still very confused about is how the initial lookup
actually works. We're mainly wondering if two similar queries that work
identically can share the same execution plan in the cache. For example, if
you ran this:
<cfquery>
Select field from table where ID = <CFQUERYPARAM value="1">
</cfquery>
Then on a different page that you wrote before you knew all about code reuse
in CFC's and stuff, you ran this:
<cfquery>
SELECT field
FROM table
WHERE id = <CFQUERYPARAM value="1">
</cfquery>
Same thing, but formatted differently. Will they both have to be compiled
into two different execution plans, or is SQL Server smart enough to use the
same plan in the cache? Is it a simple text lookup, or are formatting
characters stripped and it reduced down to bytecode or something? I have no
idea.
Thanks,
Cory
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13fkrvpprbubr9a@.corp.supernews.com...
> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for
> sproc compilations:
> a) reduce temp table usage (table variables maybe, or combine statements
> to eliminate temp storage needs altogether)
> b) create all temp objects at top of sproc
> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
> Reducing compilations from ADO/ADONET code usually requires rewriting the
> code to use more set based logic. As Andy Kelly said, on 2005 you can use
> the FORCED PARAMETERIZATION database setting. I have a client that saw a
> 30-40% throughput improvement on their bad ADO code using that setting.
>
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows
>> various things you can do to reduce the compilations per second? I'm
>> running SQL Server 2000 and I'm getting anywhere from 114 to 200
>> compilations per second, and obviously my CPU is pegged when it hits that
>> higher end. What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the
>> owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>|||If you don't parameterize your queries (which can be identified using a profiler trace), then SQL
Server does the lookup using a checksum of the whole query string. i.e., it is
"everything"-sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OqRkKMRAIHA.4164@.TK2MSFTNGP06.phx.gbl...
> We are running nearly 100% ColdFusion code. We aren't using temp tables even in the few stored
> procs that we have. We are currently rewriting the most popular apps on our sites to use
> parameters on everything. We did look into the cache on the server using some of the system
> stored procedures and we realized that the ones that were parameterized are getting used over 200
> times, while the ad-hoc queries are compiled separately with every hit, and only have one hit on
> them.
>
> Also, I posted this elsewhere:
> One thing that we are still very confused about is how the initial lookup actually works. We're
> mainly wondering if two similar queries that work identically can share the same execution plan in
> the cache. For example, if you ran this:
>
> <cfquery>
> Select field from table where ID = <CFQUERYPARAM value="1">
> </cfquery>
>
> Then on a different page that you wrote before you knew all about code reuse in CFC's and stuff,
> you ran this:
>
>
> <cfquery>
> SELECT field
> FROM table
> WHERE id = <CFQUERYPARAM value="1">
> </cfquery>
>
>
>
> Same thing, but formatted differently. Will they both have to be compiled into two different
> execution plans, or is SQL Server smart enough to use the same plan in the cache? Is it a simple
> text lookup, or are formatting characters stripped and it reduced down to bytecode or something?
> I have no idea.
>
>
> Thanks,
> Cory
>
>
>
>
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13fkrvpprbubr9a@.corp.supernews.com...
>> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for sproc compilations:
>> a) reduce temp table usage (table variables maybe, or combine statements to eliminate temp
>> storage needs altogether)
>> b) create all temp objects at top of sproc
>> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
>> Reducing compilations from ADO/ADONET code usually requires rewriting the code to use more set
>> based logic. As Andy Kelly said, on 2005 you can use the FORCED PARAMETERIZATION database
>> setting. I have a client that saw a 30-40% throughput improvement on their bad ADO code using
>> that setting.
>>
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows various things you can do to
>> reduce the compilations per second? I'm running SQL Server 2000 and I'm getting anywhere from
>> 114 to 200 compilations per second, and obviously my CPU is pegged when it hits that higher end.
>> What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>>
>|||Have a look at these to see how this works and how to address it.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
http://support.microsoft.com/kb/243588
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OqRkKMRAIHA.4164@.TK2MSFTNGP06.phx.gbl...
> We are running nearly 100% ColdFusion code. We aren't using temp tables
> even in the few stored procs that we have. We are currently rewriting the
> most popular apps on our sites to use parameters on everything. We did
> look into the cache on the server using some of the system stored
> procedures and we realized that the ones that were parameterized are
> getting used over 200 times, while the ad-hoc queries are compiled
> separately with every hit, and only have one hit on them.
>
> Also, I posted this elsewhere:
> One thing that we are still very confused about is how the initial lookup
> actually works. We're mainly wondering if two similar queries that work
> identically can share the same execution plan in the cache. For example,
> if you ran this:
>
> <cfquery>
> Select field from table where ID = <CFQUERYPARAM value="1">
> </cfquery>
>
> Then on a different page that you wrote before you knew all about code
> reuse in CFC's and stuff, you ran this:
>
>
> <cfquery>
> SELECT field
> FROM table
> WHERE id = <CFQUERYPARAM value="1">
> </cfquery>
>
>
>
> Same thing, but formatted differently. Will they both have to be compiled
> into two different execution plans, or is SQL Server smart enough to use
> the same plan in the cache? Is it a simple text lookup, or are formatting
> characters stripped and it reduced down to bytecode or something? I have
> no idea.
>
>
> Thanks,
> Cory
>
>
>
>
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13fkrvpprbubr9a@.corp.supernews.com...
>> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for
>> sproc compilations:
>> a) reduce temp table usage (table variables maybe, or combine statements
>> to eliminate temp storage needs altogether)
>> b) create all temp objects at top of sproc
>> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
>> Reducing compilations from ADO/ADONET code usually requires rewriting the
>> code to use more set based logic. As Andy Kelly said, on 2005 you can
>> use the FORCED PARAMETERIZATION database setting. I have a client that
>> saw a 30-40% throughput improvement on their bad ADO code using that
>> setting.
>>
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows
>> various things you can do to reduce the compilations per second? I'm
>> running SQL Server 2000 and I'm getting anywhere from 114 to 200
>> compilations per second, and obviously my CPU is pegged when it hits
>> that higher end. What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the
>> owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>>
>
Reducing column size
I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?
It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>
|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?
It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>
|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
Reducing column size
I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(90
0).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(90
0).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
Reducing column size
I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
Reducing Backup Size
Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.
Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
"C TO" wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.
Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
"C TO" wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
Reducing Backup Size
Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
--
"C TO" wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
--
"C TO" wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
Subscribe to:
Posts (Atom)