DB Tuning Wizard

Hi All,
I'm using the SQL Server 2005 tuning wizard and its come back with a few
suggestions.
If I implement the suggestions it estimates a -1328% improvement. Now
does this mean things will be -1328% "better" or -1328% worse
-1328% doesn't sound much like something I'd want, but then why would it
suggest I do something that would be so much worse?
Also, it suggested creating statistics on various columns. Is that
something I should look at doing? I've never played with statistics
before, just the indexes...
Thanks
Simon
Simon
What kind of suggestions? If it sugegsted creating an index or statitics and
you did that , so running the query again , have you noticed that it ran
faster?
I did some testing and followed the recommendations and saw that my
queries run much more fatser.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OQg5pVmWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm using the SQL Server 2005 tuning wizard and its come back with a few
> suggestions.
> If I implement the suggestions it estimates a -1328% improvement. Now does
> this mean things will be -1328% "better" or -1328% worse
> -1328% doesn't sound much like something I'd want, but then why would it
> suggest I do something that would be so much worse?
> Also, it suggested creating statistics on various columns. Is that
> something I should look at doing? I've never played with statistics
> before, just the indexes...
> Thanks
> Simon
|||Well, it suggested dropping a few indexes and creating stats on a few
columns.
It's really just the fact that its put a "-" in front of the suggested
improvement (e.g. a "-1200% improvement".
I'd like to know if thats nowmal. I don't have time to benchmark all the
suggestions - the db's huge and I'm not getting paid to implement the
changes. I just wanted to see how the tuning wizard worked
Thanks
Simon
|||> I'd like to know if thats nowmal. I don't have time to benchmark all the
> suggestions - the db's huge and I'm not getting paid to implement the
> changes. I just wanted to see how the tuning wizard worked
Well, I did not get "-" in front if the suggested improvment , so one thing
I know that DB TW in SQL Server 2005 is much more reliable rather than SQL
Server 2000
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ebk3lsxWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Well, it suggested dropping a few indexes and creating stats on a few
> columns.
> It's really just the fact that its put a "-" in front of the suggested
> improvement (e.g. a "-1200% improvement".
> I'd like to know if thats nowmal. I don't have time to benchmark all the
> suggestions - the db's huge and I'm not getting paid to implement the
> changes. I just wanted to see how the tuning wizard worked
> Thanks
> Simon
|||OK - Many thanks
Simon

DB Tuning Wizard

Hi All,
I'm using the SQL Server 2005 tuning wizard and its come back with a few
suggestions.
If I implement the suggestions it estimates a -1328% improvement. Now
does this mean things will be -1328% "better" or -1328% worse
-1328% doesn't sound much like something I'd want, but then why would it
suggest I do something that would be so much worse?
Also, it suggested creating statistics on various columns. Is that
something I should look at doing? I've never played with statistics
before, just the indexes...
Thanks
SimonSimon
What kind of suggestions? If it sugegsted creating an index or statitics and
you did that , so running the query again , have you noticed that it ran
faster?
I did some testing and followed the recommendations and saw that my
queries run much more fatser.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OQg5pVmWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm using the SQL Server 2005 tuning wizard and its come back with a few
> suggestions.
> If I implement the suggestions it estimates a -1328% improvement. Now does
> this mean things will be -1328% "better" or -1328% worse
> -1328% doesn't sound much like something I'd want, but then why would it
> suggest I do something that would be so much worse?
> Also, it suggested creating statistics on various columns. Is that
> something I should look at doing? I've never played with statistics
> before, just the indexes...
> Thanks
> Simon|||Well, it suggested dropping a few indexes and creating stats on a few
columns.
It's really just the fact that its put a "-" in front of the suggested
improvement (e.g. a "-1200% improvement".
I'd like to know if thats nowmal. I don't have time to benchmark all the
suggestions - the db's huge and I'm not getting paid to implement the
changes. I just wanted to see how the tuning wizard worked
Thanks
Simon|||> I'd like to know if thats nowmal. I don't have time to benchmark all the
> suggestions - the db's huge and I'm not getting paid to implement the
> changes. I just wanted to see how the tuning wizard worked
Well, I did not get "-" in front if the suggested improvment , so one thing
I know that DB TW in SQL Server 2005 is much more reliable rather than SQL
Server 2000
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ebk3lsxWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Well, it suggested dropping a few indexes and creating stats on a few
> columns.
> It's really just the fact that its put a "-" in front of the suggested
> improvement (e.g. a "-1200% improvement".
> I'd like to know if thats nowmal. I don't have time to benchmark all the
> suggestions - the db's huge and I'm not getting paid to implement the
> changes. I just wanted to see how the tuning wizard worked
> Thanks
> Simon|||OK - Many thanks
Simon

DB Tuning Wizard

Hi All,
I'm using the SQL Server 2005 tuning wizard and its come back with a few
suggestions.
If I implement the suggestions it estimates a -1328% improvement. Now
does this mean things will be -1328% "better" or -1328% worse
-1328% doesn't sound much like something I'd want, but then why would it
suggest I do something that would be so much worse?
Also, it suggested creating statistics on various columns. Is that
something I should look at doing? I've never played with statistics
before, just the indexes...
Thanks
SimonSimon
What kind of suggestions? If it sugegsted creating an index or statitics and
you did that , so running the query again , have you noticed that it ran
faster?
I did some testing and followed the recommendations and saw that my
queries run much more fatser.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OQg5pVmWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm using the SQL Server 2005 tuning wizard and its come back with a few
> suggestions.
> If I implement the suggestions it estimates a -1328% improvement. Now does
> this mean things will be -1328% "better" or -1328% worse
> -1328% doesn't sound much like something I'd want, but then why would it
> suggest I do something that would be so much worse?
> Also, it suggested creating statistics on various columns. Is that
> something I should look at doing? I've never played with statistics
> before, just the indexes...
> Thanks
> Simon|||Well, it suggested dropping a few indexes and creating stats on a few
columns.
It's really just the fact that its put a "-" in front of the suggested
improvement (e.g. a "-1200% improvement".
I'd like to know if thats nowmal. I don't have time to benchmark all the
suggestions - the db's huge and I'm not getting paid to implement the
changes. I just wanted to see how the tuning wizard worked
Thanks
Simon|||> I'd like to know if thats nowmal. I don't have time to benchmark all the
> suggestions - the db's huge and I'm not getting paid to implement the
> changes. I just wanted to see how the tuning wizard worked
Well, I did not get "-" in front if the suggested improvment , so one thing
I know that DB TW in SQL Server 2005 is much more reliable rather than SQL
Server 2000
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ebk3lsxWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Well, it suggested dropping a few indexes and creating stats on a few
> columns.
> It's really just the fact that its put a "-" in front of the suggested
> improvement (e.g. a "-1200% improvement".
> I'd like to know if thats nowmal. I don't have time to benchmark all the
> suggestions - the db's huge and I'm not getting paid to implement the
> changes. I just wanted to see how the tuning wizard worked
> Thanks
> Simon|||OK - Many thanks
Simon

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

Hi,

UserID is the promary key in Users table (like aspnet_Users) and a FK in related tables (like UserProfile etc). Since each profile can be identified by the userID only, there is nothing wrong in having it as the PK of the same table. Why would you like to have a surrogate key for such a table (when it doesnt really need one). IMHO, this is fine.

Vivek

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

This approach -- using the same name in the related table as in the

primary -- is appropriate when the related table is a subset of the

objects in the primary table, rather than a linking table between two or more primary entities, and has a

one-to-one relationship to the primary table. Usually the related

table is created to avoid nulls in a core table.

A product

inventory might be an example. It makes sense to have a unique

product_id for each product manufactured by a company. This would of

course be the primary key for the master "products" table. You would

not want to have information specific to a particular line of producs

in this table, though. Say the comapny makes tools, and one of their

lines is sockets. Fields such as socket_type [e.g. hex, star],

drive_size, or socket_size would be irrelevant to many other tools, but

quite necessary to the sockets line. Therefore, you might have a data

structure like:

tools (
product_id char(12) primary key,
tool_class varchar(64),
price decimal,
...
);

sockets (
product_id char(12) primary key references tools(product_id),
socket_type smallint,
drive_size decimal,
socket_size decimal,
...
);

This

works because all sockets are tools, though many tools are not

sockets. Likewise, I would choose product_id here, rather than say

tool_id, for flexibility. The company may also carry other lines of

products for which tool_class would be an inappropriate field. They

are still all products, though.

Hope this helps,

Joseph

DB Objects stored in VSS

Way Way Way back, IIRC there was discussion that SQL Server 2005 would integrate into Source Safe in such a way, that you could version your objects to Source Safe.

Is this still there, or was it ever there? I know there are third party tools that can do it, but none of them seem to be updated to support SQL2005, and would would rather not use a third party tool.

So basically, short of managing SCRIPT files via Source Safe, can it be done?

No. As far as I know, the plan was to allow users to script any object to a file and manage the script in Visual Source Safe.|||

I think there are just too many issues with MS's VSS that it is better to direct the effort to an entity solely focused on this utility. I would not shy away from MS on this but in the meantime I advocate the use of a better alternative.

VSS will be fixed in due time but not as of yet.

DB Objects stored in VSS

Way Way Way back, IIRC there was discussion that SQL Server 2005 would integrate into Source Safe in such a way, that you could version your objects to Source Safe.

Is this still there, or was it ever there? I know there are third party tools that can do it, but none of them seem to be updated to support SQL2005, and would would rather not use a third party tool.

So basically, short of managing SCRIPT files via Source Safe, can it be done?

No. As far as I know, the plan was to allow users to script any object to a file and manage the script in Visual Source Safe.|||

I think there are just too many issues with MS's VSS that it is better to direct the effort to an entity solely focused on this utility. I would not shy away from MS on this but in the meantime I advocate the use of a better alternative.

VSS will be fixed in due time but not as of yet.

DB Objects stored in VSS

Way Way Way back, IIRC there was discussion that SQL Server 2005 would integrate into Source Safe in such a way, that you could version your objects to Source Safe.

Is this still there, or was it ever there? I know there are third party tools that can do it, but none of them seem to be updated to support SQL2005, and would would rather not use a third party tool.

So basically, short of managing SCRIPT files via Source Safe, can it be done?

No. As far as I know, the plan was to allow users to script any object to a file and manage the script in Visual Source Safe.|||

I think there are just too many issues with MS's VSS that it is better to direct the effort to an entity solely focused on this utility. I would not shy away from MS on this but in the meantime I advocate the use of a better alternative.

VSS will be fixed in due time but not as of yet.

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyan
Hi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:

> about 1 week back, I created a DB dbo as owner. After this i never touched
> this db. We have about 5 DBA with full previlage. Suddlenly when i checked
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find when
> owner ship is changed.?
> thanks
> kalyan

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyanHi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:

> about 1 week back, I created a DB dbo as owner. After this i never touche
d
> this db. We have about 5 DBA with full previlage. Suddlenly when i checke
d
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n
't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find wh
en
> owner ship is changed.?
> thanks
> kalyan

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyanHi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:
> about 1 week back, I created a DB dbo as owner. After this i never touched
> this db. We have about 5 DBA with full previlage. Suddlenly when i checked
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find when
> owner ship is changed.?
> thanks
> kalyan

Db Logic - Cant put it back together

Hi there!

Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.

Overview:

I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.

Layout for a magazine will look something like this:

Size Trim Type Bleed

Full Page 280x440 270x430 290x450

Half Page 140x220 130x210 150x230

etc...

Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.

Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.

Break it down!

Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).

I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!

The Problem.

The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.

I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.

Any ideas on how i could manage this?

If you made it tis far through my question then thanks anyways! I hope you can help me out!

Ta

You'll need to use SQL Servers new PIVOT function. Assuming your tables are set up as such:

MagSize: ID, Size
MagType: ID, TypeName
MagAttribute: SizeID, TypeID, Value

You generate your desired results with:

WITH TempTable
AS (
SELECT Size, TypeName, Value FROM
MagSize s
INNER JOIN MagAttribute v ON s.ID = v.SizeID
INNER JOIN MagType t ON t.ID = v.TypeID
)
SELECT * FROM TempTable
PIVOT (
MAX(VALUE)
FOR TypeName IN ([Trim],[Type],[Bleed])
) AS PVT

MSDN Article|||

Hi northside!

Thanks for your reply!

Ok seems like im gonna be stuck since i only have access to a SQL 2000 server.

I was just wondering from your query where you used the Trim, Type, Bleed values in your second last line: Because i dont know if every magazine will have three attributes (some might only have Trim and Bleed) Is there some way i can 1) replace Trim, Type, Bleed with variables and 2) use some array maybe to generate the quantity of attributes?

See I recon I can do this if I can create DDL based on the results created by the SELECT query. But my logic tells me ill need to store results in an array and i dunno if t-sql caters for that. So far i couldn't find anything.

Thanks once again!

|||

You can still do pivot queries in SQL Server 2000, it's just a bit more convoluted. The query below does the equivalent to the above:

SELECT s.Size,

MAX(CASE WHEN TypeName = 'Trim' THEN Value END) Trim,

MAX(CASE WHEN TypeName = 'Type' THEN Value END) Type,

MAX(CASE WHEN TypeName = 'Bleed' THEN Value END) Bleed

FROM

MagSize s

INNER JOIN MagAttribute v ON s.ID = v.SizeID

INNER JOIN MagType t ON t.ID = v.TypeID

GROUP BY Size

Unfortunately, neither the above query and SQL Server 2005s pivot function are dynamic - you must know in advance what columns you want to pivot. If you want a more dynamic report, you have to write an ad-hoc query. Something like the one below should hopefully get you started:

CREATE PROCEDURE [dbo].[MagazinePivot]

@.ID int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.pivot VARCHAR(2000)

SELECT @.pivot = COALESCE(@.pivot + ', ', '') + 'MAX(CASE WHEN TypeName = '''+ TypeName + ''' THEN Value END) ' + TypeName

FROM MagType WHERE ID IN (

SELECT TypeID FROM MagAttribute WHERE SizeID = @.ID

)

DECLARE @.sql VARCHAR(2000)

SET @.sql = '

SELECT s.Size, ' + @.pivot + '

FROM

MagSize s

INNER JOIN MagAttribute v ON s.ID = v.SizeID

INNER JOIN MagType t ON t.ID = v.TypeID

GROUP BY Size'

EXEC (@.sql)

END

|||

northside!

Dude quality post!

Gonna take me a while to work through this one though.

Thanks bud

DB lock in SQL Server

HI,

I am new to SQL Server thing. I am facing a problem with locking in SQL Server.
2 days back my all the DML statements were giving the timeout error. Using the Enterprise Manager I was able to find that there were 2 DB locks in the database.
Then after a day or so I found that one of them is automatically killed, but one is still there. But this time I am able to execute DML statements.

How this is possible. the lock was DB lock.
This is urgent. Thanks in advance..Hi,
In the enterprise manager, having admin rights, you can look at what is going on inside your SQL Server. The EM will show that the database has locks but you have to pay attention when it is blocked. In the EM under Management, refresh current activity and look at expand locks/Process ID. If you do not have (blocked) next to anything you are free to do what you need to do. Also you can open up Process info and gather more information as to who is holding the lock and the code represented.
Adam|||Thanks

But my problem is DB lock. As the Enterprise Manager displays that the lock on the database is DB lock then how can I execute the DML on the database. The DB lock type was shared.