Registering Error: working

Showing posts with label working. Show all posts

Friday, March 30, 2012

Relatively easy SQL SELECT statement issues :/

Hi All,

I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:

CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;

CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;

CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;

The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.

What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!! :confused:

Heres my current query:

SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid

Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?

Thanks in advance to anyone who can help

Damocles.SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid|||select business_contact.Title
, business_contact.Surname
, business_contact.FirstName
, company.Name
, company.Manager
from business_contact
left outer
join works_for
on business_contact.BusContactID
= works_for.BusContactID
left outer
join company
on works_for.CompanyID
= company.CompanyID|||Thanks to both for your replies, works great!

I could really do to read a decent SQL tutorial at some point!

Damocles.

Wednesday, March 28, 2012

Relationships getting deleted (MS SQL)

Dear All,

I got a wierd problem which I haven't been able to explain.

I am working on MS SQL 2000. I don't know for what reason, the
relationship between Parent/Child table is getting deleted. When I
open up the ER diagram in MSSQL Enterprise Manager, I see the
relationship line come up for just a split of a second, and disappears
afterwards. This is the second time I am seeing this thing.

I realized it when I accidently deleted the rows in the Parent Table.
They should not have been deleted as I had associated records in the
Child Table (because of Key Constraints).

Has anyone ever come across this situation? Do you have any
suggestions?

Regards,
TinTin"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0410071018.663f4082@.posting.google.c om...
> Dear All,
> I got a wierd problem which I haven't been able to explain.
> I am working on MS SQL 2000. I don't know for what reason, the
> relationship between Parent/Child table is getting deleted. When I
> open up the ER diagram in MSSQL Enterprise Manager, I see the
> relationship line come up for just a split of a second, and disappears
> afterwards. This is the second time I am seeing this thing.
> I realized it when I accidently deleted the rows in the Parent Table.
> They should not have been deleted as I had associated records in the
> Child Table (because of Key Constraints).
> Has anyone ever come across this situation? Do you have any
> suggestions?
> Regards,
> TinTin

The best suggestion is probably don't use EM for this task - it has a
history of glitches and small bugs, and some tasks are simply not possible
at all. The most reliable way to manage your database objects is with TSQL
in Query Analyzer, as you have complete control over what's happening, and
you can easily save scripts for common tasks.

In this case, you can look at sp_help, sp_pkeys and sp_fkeys to get
information about the table and its current primary and foreign keys, and
ALTER TABLE to add or remove constraints.

Simon

Relationship Problem

Hello all,

OK - my problem is that my relationship(s) just doesn't seem to work.
I am currently using Microsoft SQL Server 2005, and working through the management studio. For my site coding, I'm using Coldfusion.
So you can expect that I don't know an awful lot of actual SQL code, and instead am relying on the interface.

I have already achieved making a fully functional registeration and login page for my site, so there's no hiccups there :)
But when I insert a new user to my "users" table, the userid doesn't seem to appear my other table; "characters". I should probably note that by characters, I mean personalities/people - not actual text characters or whatnot.

Here's the low-down on my current DB layout:
My first table is "users" and has the columns "userid", "username", "password", and "emailaddress". The userid column is a PK with identity specification. It is also an int datatype, while the other columns are varchar(20)s. Also, the userid doesn't allow nulls, obviously.

My second table is "characters" and has several columns, mostly of the same datatypes as the "users" table - so it's not relevant. The two important columns are "characterid" and "userid" - both are int datatypes and do not allow nulls. The characterid column serves as the PK and has identity specification.

Now comes the problem...; using a database diagram, I have created a relationship between "userid" from "users", and "userid" from "characters". The "userid" in "characters, in theory, is the FK.
Right. So I register myself on the site using a simple insert sql query (actually, I use <cfinsert>) - the insert goes directly into "users" with a username, password, and email address. A unique number is created in userid, as you'd expect... but nothing happens in "characters" at all!
How do I get the userid to pass on from "users" into "characters"?

Well crap, this post is extremely long now. I only hope it's understandable, and that someone can help me :(

Thanks a lot in advance!
AidenFK is a logical object and not physical that can insert record for you.
You have to create a second insert statement into a characters table but getting newly created userid first.

Good Luck.|||Hmm, thanks for the reply..

I think I tried doing that already.
I first used the insert sql statement and inserted a username, password, and email address into the "users" table - I can confirm that this was successful. That then created the userid automatically.
Then, another insert sql statement was ran straight after, which also inserted information into fullname, gender, and class in the "characters" table - this also was successful. The characterid was successfully added automatically, but nothing happened in the userid column (in "characters").

Am I going about this right?

More on the subject... I was under the impression that relationships were used to automatically make specific entries from a column in one table, copy over to a similar column in another table - therefore making queries from different tables always follow the same userid (for example). This would then make sure the site user always views only his or her row information, no matter the table queried.|||You have a wrong impression.
In second insert statement instead of userid use following statement
Insert into characters( "userid", ) values( (select userid from users where username = "username" and password = "password" and emailaddress = "emailaddress"), other columns here)
So my point is you can use select statement instead of userid.
FK works like this:
If you would try entering some userid into characters table that does not exist in users table it will give you an error.
I would recommend creating unique index on users table over (username, password, emailaddress) columns so you wouldnt have duplicates which might cause problem at insert time. If you want to have multiple records for the same user then you can change your insert statement to this:
Insert into characters( "userid", ) values( (select max(userid) from users where username = "username" and password = "password" and emailaddress = "emailaddress"), other columns here)

Good Luck.|||I understand now :) I guess I was expecting something more automated with the use of relationships.

But I still don't entirely understand the point in having a relationship.
Even without the relationship, I can still add the userid from "users" into "characters" in the way that you described. So long as I have an identity specification on my userid in "users", there will never be an error related to a duplicate userid.
EDIT: I could also use code to check that the userid is currently available and exists from "users", before trying to create it within "characters", thus eliminating the need for a relationship? I think?...

That aside, your solution has been very helpful to me :) I can finally continue with the rest of my site!
Thanks
Aiden|||select userid from users where username = "username" and password = "password" and emailaddress = "emailaddress"

Statement above can return 2 different userids it will be generated for you but it will be escentially the same user. If you have identity column you can use @.@.IDENTITY global variable in the next statement instead of select statement above.

Foreign key just to make sure primary key exists in a parent table.

Godd Luck.

Friday, March 23, 2012

related rooms query

hi, im working on this for a long time. i'm using MSsql-server2000
i have a table [visits] that records users visits to rooms. the columns are
room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related
to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.Hi

Please post DDL (Create table statements you can use the generate SQL script
option in EM), example data (as insert statements), expected output and your
current queries. That will remove any ambiguity

It is not clear how you relate a users movement from one room to another,
what if the user has two browsers or shortcuts to specific rooms?

John

"Uri Lazar" <arielazar@.bezeqint.net> wrote in message
news:3f89b64e@.news.bezeqint.net...
> hi, im working on this for a long time. i'm using MSsql-server2000
> i have a table [visits] that records users visits to rooms. the columns
are
> room_id, user_id, visits.
> i want to write a query that can calculate the top 10 rooms that are
related
> to any given room. i was thinking of firstly making a function that counts
> how many users visited both room A and room B, and then running this
> function on A and all other rooms, and order by the result. i keep getting
> weird errors when doing that. please elaborate.|||Without DDL and example data I'm not sure I've fully understood your
requirement. Here's some assumed DDL and sample data:

CREATE TABLE RoomVisits (roomid INTEGER NOT NULL /* REFERENCES Rooms
(roomid) */, userid INTEGER NOT NULL /* REFERENCES Users (userid) */, visits
INTEGER NOT NULL CHECK (visits>0), PRIMARY KEY (roomid, userid))

INSERT INTO RoomVisits VALUES (1,100,1)
INSERT INTO RoomVisits VALUES (2,100,1)
INSERT INTO RoomVisits VALUES (3,100,4)
INSERT INTO RoomVisits VALUES (4,100,2)
INSERT INTO RoomVisits VALUES (1,222,2)
INSERT INTO RoomVisits VALUES (2,222,4)

Apparently for each room "A" you want the top 10 related rooms "B", ordered
by total number of visits to B. Rooms are deemed related if any user has
visited both - is that correct? If so, it seems a slightly artificial
requirement. Surely by that definition if users are making tours of rooms
then every room will inevitably become related to every other, unless there
are many more rooms than users.

Anyway, here's the query. First create a view which lists each related A-B
combination and the corresponding total number of visits to B.

CREATE VIEW Related_Room_Visits (room_A, room_B, visits_to_B)
AS
SELECT A.roomid, B.roomid, MAX(C.tot_visits)
FROM RoomVisits AS A
JOIN RoomVisits AS B
ON A.userid = B.userid AND A.roomid <> B.roomid
JOIN
(SELECT roomid, SUM(visits) AS tot_visits
FROM RoomVisits
GROUP BY roomid) AS C
ON B.roomid = C.roomid
GROUP BY A.roomid, B.roomid

Now display just the Top N for each room A. For my example data I've just
specified TOP 2 but you can change this as required:

SELECT R1.room_A, R1.room_B, R1.visits_to_B
FROM Related_Room_Visits AS R1
JOIN Related_Room_Visits AS R2
ON R1.room_A=R2.room_A AND R1.visits_to_B <= R2.visits_to_B
GROUP BY R1.room_A, R1.room_B, R1.visits_to_B
HAVING COUNT(*) <= 2 /* Top 2 for each Room_A */
ORDER BY R1.room_A, R1.room_B, R1.visits_to_b DESC

If this doesn't help then please post DDL, post some sample data as INSERT
statements and give an example of your required result.

--
David Portas
----
Please reply only to the newsgroup
--

Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.
Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:

>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>
|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
sql

Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA

Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:

>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They ar
e
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I kno
w
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA

Reinstalling SQL Server on a Cluster

OK... Here's the deal. We had a 2 server cluster set up with SQL Server.
This consultant we were working with uninstalled the cluster and then
uninstalled SQL Server. Now we set the cluster back up, but we're having
trouble re-installing SQL Server. Can someone point me to directions on
re-installing SQL Server on a cluster?
ThanksHi
http://support.microsoft.com/default.aspx?scid=kb;en-us;888121
http://support.microsoft.com/default.aspx?scid=kb;en-us;243218
http://support.microsoft.com/default.aspx?scid=kb;en-us;254321
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Michael C#" <xyz@.abcdef.com> wrote in message
news:VXo_d.26380$k66.16250@.fe09.lga...
> OK... Here's the deal. We had a 2 server cluster set up with SQL Server.
> This consultant we were working with uninstalled the cluster and then
> uninstalled SQL Server. Now we set the cluster back up, but we're having
> trouble re-installing SQL Server. Can someone point me to directions on
> re-installing SQL Server on a cluster?
> Thanks
>
>|||Thank you!
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OoEIY80KFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Hi
> http://support.microsoft.com/default.aspx?scid=kb;en-us;888121
> http://support.microsoft.com/default.aspx?scid=kb;en-us;243218
> http://support.microsoft.com/default.aspx?scid=kb;en-us;254321
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:VXo_d.26380$k66.16250@.fe09.lga...
>> OK... Here's the deal. We had a 2 server cluster set up with SQL Server.
>> This consultant we were working with uninstalled the cluster and then
>> uninstalled SQL Server. Now we set the cluster back up, but we're having
>> trouble re-installing SQL Server. Can someone point me to directions on
>> re-installing SQL Server on a cluster?
>> Thanks
>>
>|||Just FYI - ended up having to completely uninstall SQL Server from the
cluster, and then had to go into the Registry and remove the remnants of the
SQL Server installation (HKLM\Software\Microsoft\MSSQL and \Microsoft SQL
Server keys), then reinstall on the cluster. For some reason when I
uninstalled the first time, it kept a reference to the prior virtual SQL
Server installation, and wouldn't let me overwrite it.
Thanks for the help!
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Wsq_d.32303$eh1.25736@.fe11.lga...
> Thank you!
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OoEIY80KFHA.2252@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;888121
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;243218
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;254321
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Michael C#" <xyz@.abcdef.com> wrote in message
>> news:VXo_d.26380$k66.16250@.fe09.lga...
>> OK... Here's the deal. We had a 2 server cluster set up with SQL
>> Server.
>> This consultant we were working with uninstalled the cluster and then
>> uninstalled SQL Server. Now we set the cluster back up, but we're
>> having
>> trouble re-installing SQL Server. Can someone point me to directions on
>> re-installing SQL Server on a cluster?
>> Thanks
>>
>>
>

Reinstalling SQL Server on a Cluster

Tuesday, March 20, 2012

re-insert / templating records

I have a problem. The project that I'm working on requires me to duplicate
records. As in a series of records are entered into the system the user then
click on a button to make these as 'Templates' so that they would not have
to re-enter alot of the information. So from a db perspective I would have
to re-insert these records into the database. The problem is there're over
20 tables and the relationship is complex. Is there a way to copy the parent
the record and have sqlserver automatically cascade and re-insert all
related and referenced records back into the database ?

or is there a easy way to do this ?

Thanks
TomDuplicating rows in a table should never be necessary or desirable and it
shouldn't even be possible since evey table should have unique/primary key
constraints that prevent this. I assume therefore you will want to maintain
uniqueness by changing some column values. Unfortunately you haven't told us
anything about keys, constraints or the data you want to modify.

> Is there a way to copy the parent
> the record and have sqlserver automatically cascade and re-insert all
> related and referenced records back into the database ?

I guess here that you are talking about copying rows between tables with
IDENTITY columns. This is easy provided you have declared natural (not
IDENTITY) keys on the tables. IDENTITY should not be the only key of a
table. Here is an example of moving a parent entity and its related rows
between tables while maintaining the surrogate keys.

CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE /* Note the Key */)

CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE /* Note the Key */, employeename VARCHAR(30) NOT
NULL, deptid INTEGER NOT NULL REFERENCES Departments (deptid))

CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE)

CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES New_Departments (deptid))

INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D
LEFT JOIN New_Departments AS N
ON D.deptname = N.deptname
WHERE N.deptname IS NULL

INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname
LEFT JOIN New_Employees AS E2
ON E1.ssn = E2.ssn
WHERE E2.employeeid IS NULL

--
David Portas
SQL Server MVP
--|||Tom Gao (tomgaomail@.optushome.com.au) writes:
> I have a problem. The project that I'm working on requires me to
> duplicate records. As in a series of records are entered into the system
> the user then click on a button to make these as 'Templates' so that
> they would not have to re-enter alot of the information. So from a db
> perspective I would have to re-insert these records into the database.
> The problem is there're over 20 tables and the relationship is complex.
> Is there a way to copy the parent the record and have sqlserver
> automatically cascade and re-insert all related and referenced records
> back into the database ?

There's a whole lot of information missing here, but in any case, the
answer is: no.

Are you inserting into the same table, or from a table with templates?
Well, in most cases it makes sense to store templates in the same table
as the real rows.

But then there are at least two columns that are not to be copied to
the new rows: the key and the column that marks that the template is a
template.

And I would not be surprised if there are more columns. For instance,
say that there are auditing columns who tells which which user that
created the row and when. Such data is of course not possible to
inherit from the client.

So, I am sorry, you just have to start coding. And pay attention to
the business requirements, so that you copy what you should copy, no
more, no less.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Reinitializing subscriptions not working

Hi,

I'm doing merge sync between SQL Compact on mobile devices and SQL Server 2005. I recently made a schema change on the server and noticed that it caused an error on the subscribers when syncing. I fixed the schema problem, and did a reinitialize all subscriptions with a new snapshot. On the mobile devices I forced a reinitialisation (upload changes first). I'm still getting the same error message, which relates to a schema change which is no longer relevant to the server db and snapshot. For some reason the subscriber dbs are trying to reapply the original problem schema change even though they're supposed to be reinitializing to a new snapshot. I've even tried dropping the affected tables from the publication, and reinitializing all subscriptions with a new snapshot, but still the same error on the subscribers. Does anyone know why the subscriber dbs are still trying to apply a now defunct schema change (on tables which no longer exist in the publication)? Is there any way to flush this from the subscribers so they correctly pick up the new snapshot?

Regards,

Greg McNamara

Some additional information on the above:

The "rogue" schema change appears to be coming from the publisher, and not cached on the subscriber. The basic problem is that I'm trying to reinitialize subscribers, but it's trying to make a schema change on the subscribers which was made before the current snapshot was created. My understanding of reinitialization was that it would upload subscriber changes and then basically rebuild the subscriber db from the snapshot. Instead it seems to be applying incremental, historical schema changes on the subscriber.

Hope someone can help me with this.

Regards,

Greg McNamara

|||

And more info:

The schema change causing the original problem was the addition of a foreign key constraint. Records in a table were deleted on the server db before adding the constraint but the subscriber dbs still contain records. The sync is failing because it's trying to apply a constraint against existing records and failing. Reinitializing the subscribers is not fixing the problem (as detailed above). I decided to try deleting the records on the subscriber db before re-syncing. I now get a different error message on sync:

"Either the cursor is not on a row or there are no rows left"

Apparently this is a SQL Compact engine internal error. The malfunctioning reinitialization function seems to have caused this. I tried a repair/compact on the db but still get the same error message on syncing.

Back to the reinitialize not using the current snapshot, is anyone aware of places I should look to see if an old snapshot is being cached and somehow used by the merge sync agent?

Regards,

Greg McNamara

Friday, March 9, 2012

Reindex depleting Working Set

I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
Server Memory setting is 0, and Max is set at 14 GB.
When I reindex (ALTER INDEX...REBUILD) a database that is approximately 6GB
in size, the server grinds to a halt and the error log contains errors
similar to the following:
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
Reporting Services is also running on this box, but when the Reindex is
taking place, the Reindex job is the only active SPID.
The "PF Usage" in Task Manager is at 15.3 GB.
Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Hi
Check you are locking pages in memory see:
http://support.microsoft.com/kb/918483
John
"cbrichards via SQLMonster.com" wrote:
> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||John,
Not applicable to my situation. Lock Pages in Memory is ignored when running
Standard Edition.
Still in need of help!
John Bell wrote:
>Hi
>Check you are locking pages in memory see:
>http://support.microsoft.com/kb/918483
>John
>> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
>> Server Memory setting is 0, and Max is set at 14 GB.
>[quoted text clipped - 13 lines]
>> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
Have you tried lowering the max memory and setting the minimum to the same
value (say 12GB)?
John
"cbrichards via SQLMonster.com" wrote:
> John,
> Not applicable to my situation. Lock Pages in Memory is ignored when running
> Standard Edition.
> Still in need of help!
> John Bell wrote:
> >Hi
> >
> >Check you are locking pages in memory see:
> >
> >http://support.microsoft.com/kb/918483
> >
> >John
> >
> >> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
> >> Server Memory setting is 0, and Max is set at 14 GB.
> >[quoted text clipped - 13 lines]
> >>
> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I have heard of that option, but I do not have the understanding of SQL
Servers memory structures to know how that would change my reindexing issue.
From what I understand is happening with my reindexing, is that the
reindexing is consuming all 14GB dedicated to SQL Servers buffer pool and
from there, needs even more memory, which is then going to the Page File.
Whether that is correct or not, I do not know, I just know that the Working
Set is being trimmed.
Are you saying that setting the Max and Min server memory settings to the
same value, that this will eliminate Page File usage when reindexing? I know
it will prevent the Working Set from being trimmed, but does that eliminate
Page File swapping?
John Bell wrote:
>Hi
>Have you tried lowering the max memory and setting the minimum to the same
>value (say 12GB)?
>John
>> John,
>> Not applicable to my situation. Lock Pages in Memory is ignored when running
>[quoted text clipped - 15 lines]
>> >>
>> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
By setting a lower maximum then it may allow other processes to be allocated
memory without SQL Server grabbing/regrabbing it.
John
"cbrichards via SQLMonster.com" wrote:
> I have heard of that option, but I do not have the understanding of SQL
> Servers memory structures to know how that would change my reindexing issue.
> From what I understand is happening with my reindexing, is that the
> reindexing is consuming all 14GB dedicated to SQL Servers buffer pool and
> from there, needs even more memory, which is then going to the Page File.
> Whether that is correct or not, I do not know, I just know that the Working
> Set is being trimmed.
> Are you saying that setting the Max and Min server memory settings to the
> same value, that this will eliminate Page File usage when reindexing? I know
> it will prevent the Working Set from being trimmed, but does that eliminate
> Page File swapping?
> John Bell wrote:
> >Hi
> >
> >Have you tried lowering the max memory and setting the minimum to the same
> >value (say 12GB)?
> >
> >John
> >
> >> John,
> >> Not applicable to my situation. Lock Pages in Memory is ignored when running
> >[quoted text clipped - 15 lines]
> >> >>
> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So you are saying that other processes are chewing up SQL Servers Working Set,
and that by lowering SQL Servers Working Set (from 14Gb to 12Gb) it will
still have enough Working Set memory?
John Bell wrote:
>Hi
>By setting a lower maximum then it may allow other processes to be allocated
>memory without SQL Server grabbing/regrabbing it.
>John
>> I have heard of that option, but I do not have the understanding of SQL
>> Servers memory structures to know how that would change my reindexing issue.
>[quoted text clipped - 22 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via http://www.sqlmonster.com|||WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
not just SQL Server.
John is suggesting that you reduce the amount that SQL Server is asking for
in the first place.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79ca83aaaea1d@.uwe...
> So you are saying that other processes are chewing up SQL Servers Working
> Set,
> and that by lowering SQL Servers Working Set (from 14Gb to 12Gb) it will
> still have enough Working Set memory?
> John Bell wrote:
>>Hi
>>By setting a lower maximum then it may allow other processes to be
>>allocated
>>memory without SQL Server grabbing/regrabbing it.
>>John
>> I have heard of that option, but I do not have the understanding of SQL
>> Servers memory structures to know how that would change my reindexing
>> issue.
>>[quoted text clipped - 22 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via http://www.sqlmonster.com
>|||Okay, as far as I can tell the reindexing is the only active SPID and this
reindexing is depleting the Working Set.
So you are saying that reducing the amount SQL Server is asking for, will in
turn not deplete the Working Set any more than it is currently? Does this
mean that reducing the amount SQL Server is asking for (reducing from 14GB to
12Gb) will make the Working Set larger?
Jay wrote:
>WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
>not just SQL Server.
>John is suggesting that you reduce the amount that SQL Server is asking for
>in the first place.
>> So you are saying that other processes are chewing up SQL Servers Working
>> Set,
>[quoted text clipped - 15 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||What do you mean when you say "Working Set"?
I think you mean the (Windows) perfmon counter that measures the total
demands against virtual memory. However, I have no idea how that concept can
include the term "deplete".
Since it measures the TOTAL, if you reduce the amount of memory demanded,
then the TOTAL demanded will also be reduced.
Beyond that, just try what John suggested without completely understanding
everything first. You will probably be happy with the result and once you
see it, you'll understand better.
Jay
PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
soonest.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79cc0a5c0e9bb@.uwe...
> Okay, as far as I can tell the reindexing is the only active SPID and this
> reindexing is depleting the Working Set.
> So you are saying that reducing the amount SQL Server is asking for, will
> in
> turn not deplete the Working Set any more than it is currently? Does this
> mean that reducing the amount SQL Server is asking for (reducing from 14GB
> to
> 12Gb) will make the Working Set larger?
> Jay wrote:
>>WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
>>not just SQL Server.
>>John is suggesting that you reduce the amount that SQL Server is asking
>>for
>>in the first place.
>> So you are saying that other processes are chewing up SQL Servers
>> Working
>> Set,
>>[quoted text clipped - 15 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||When I say "Working Set" I am referring to SQL Servers Working Set, not the
perfmon counter. This is the error in my SQL Error Log that I posted at the
beginning of this thread:
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
And while I believe John's suggestion may be the solution, I really need to
be able to explain it to my team, before I just make a wild configuration
change.
Which gets back to my latest inquiry:
As far as I can tell the reindexing is the only active SPID and this
reindexing is depleting the Working Set.
And you are saying that reducing the amount SQL Server is asking for, will in
turn not deplete the Working Set any more than it is currently? Does this
mean that reducing the amount SQL Server is asking for (reducing from 14GB to
12Gb) will make the Working Set larger?
Jay wrote:
>What do you mean when you say "Working Set"?
>I think you mean the (Windows) perfmon counter that measures the total
>demands against virtual memory. However, I have no idea how that concept can
>include the term "deplete".
>Since it measures the TOTAL, if you reduce the amount of memory demanded,
>then the TOTAL demanded will also be reduced.
>Beyond that, just try what John suggested without completely understanding
>everything first. You will probably be happy with the result and once you
>see it, you'll understand better.
>Jay
>PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
>soonest.
>> Okay, as far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>[quoted text clipped - 19 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||cbrichards,
I decided to answer your last post first (below) and then look at the
problem from the beginning. You don't really care about Working Sets, paging
and max memory, you just want to re-index a table.
If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
will see it is a rich and fully featured command (I was amazed when I first
read it). There are ways you can make it less intrusive to your system.
At the top of my list would be the following three options:
From BOL:
--
REORGANIZE
Specifies the index leaf level will be reorganized. This clause is
equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
performed online. This means long-term blocking table locks are not held and
queries or updates to the underlying table can continue during the ALTER
INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled
index or an index with ALLOW_PAGE_LOCKS set to OFF.
-and-
The rebuild operation can be minimally logged if the database recovery model
is set to either bulk-logged or simple. For more information, see Choosing a
Recovery Model for Index Operations.
--
and rebuilding one index at a time, not using the ALL option.
I'm not sure how the logging change will affect memory, but it could easily
help. One thing is for sure, your .ldf file won't bloat.
Reorganize is the option I always go for first when dealing with indexes.
It's lower impact overall and will frequently do the job. There might even
be more options that can help you in there, but I can't tell you because I
don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
tell you the fragmentation within the index.
Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
indexes where the defrag will do no good.
On to current answers.
> When I say "Working Set" I am referring to SQL Servers Working Set, not
> the
> perfmon counter. This is the error in my SQL Error Log that I posted at
> the
> beginning of this thread:
It is the same thing. The Working Set (for the 3rd time) is the TOTAL demand
for memory. Your total memory is RAM + (Page File size - RAM). If you go
over the RAM you have available, you start to page.
Please read the following link. It is a short article and very good.
http://support.microsoft.com/kb/555223
> And while I believe John's suggestion may be the solution, I really need
> to
> be able to explain it to my team, before I just make a wild configuration
> change.
His suggestion was to reduce SQL Server's MAX memory setting so that it
didn't ask for as much from Windows. Hardly a "wild configuration change".
> Which gets back to my latest inquiry:
> As far as I can tell the reindexing is the only active SPID and this
> reindexing is depleting the Working Set.
The SPID you are looking at is in SQL Server and therefore controlled by the
server's settings.
> And you are saying that reducing the amount SQL Server is asking for, will
> in
> turn not deplete the Working Set any more than it is currently? Does this
> mean that reducing the amount SQL Server is asking for (reducing from 14GB
> to
> 12Gb) will make the Working Set larger?
Sigh. No, it will not make the working set larger, it will make it smaller -
which is what you want. You seem to think the Working Set is the pool of
memory you are drawing from, it is not. The Working Set is drawing from
system memory.
> Jay wrote:
>>What do you mean when you say "Working Set"?
>>I think you mean the (Windows) perfmon counter that measures the total
>>demands against virtual memory. However, I have no idea how that concept
>>can
>>include the term "deplete".
>>Since it measures the TOTAL, if you reduce the amount of memory demanded,
>>then the TOTAL demanded will also be reduced.
>>Beyond that, just try what John suggested without completely understanding
>>everything first. You will probably be happy with the result and once you
>>see it, you'll understand better.
>>Jay
>>PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
>>soonest.
>> Okay, as far as I can tell the reindexing is the only active SPID and
>> this
>> reindexing is depleting the Working Set.
>>[quoted text clipped - 19 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||1) is this a repeatable error?
2) do you have other applications/services running on the server?
3) Is this an HP box? If so, they have a VERY nasty bug in 64 bit in their
iLO system that will flush memory.
4) Check if you are doing any large-file copies at the time. Win2003 64 bit
ALSO has a very nasty bug that will flush memory.
5) only 2GB available is probably not enough even if you don't have other
stuff running other than just the sql relational engine (things such as
reporting services, iis, analysis services, etc, etc)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79bdf69e9af82@.uwe...
>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
> 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks Jay for the detailed response and the link to RAM, Virtual Memory, and
the Paging File.
I am still not understanding some things.
So the Working Set is drawing from system memory, and when you say system
memory I interpret that as being RAM + Paging File.
In decreasing the Max Server memory I therefore decrease the Working Set
dedicated to SQL Server.
From the following two error messages I read that the sql server process is
utilizing (Max Server or Working Set) memory 0% and 12% respectively.
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 0 seconds. Working set
(KB): 93180, committed (KB): 15052932, memory utilization: 0%.
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 328 seconds. Working set
(KB): 609700, committed (KB): 4931236, memory utilization: 12%.
My interpretation to my issue of not being able to reindex, is that this
reindexing job exceeds the amount of RAM available and the OS is moving pages
out of SQL Servers Working Set (comparing the (KB) in the the error log
entries above for the Working Set). If in setting the Max Server memory to a
lower setting decreases the Working Set, and the error log shows the Working
Set as suffering already when under stress, I do not see how lowering Max
Server memory will help.
Please clarify.
Jay wrote:
>cbrichards,
>I decided to answer your last post first (below) and then look at the
>problem from the beginning. You don't really care about Working Sets, paging
>and max memory, you just want to re-index a table.
>If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
>will see it is a rich and fully featured command (I was amazed when I first
>read it). There are ways you can make it less intrusive to your system.
>At the top of my list would be the following three options:
>From BOL:
>--
> REORGANIZE
> Specifies the index leaf level will be reorganized. This clause is
>equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
>performed online. This means long-term blocking table locks are not held and
>queries or updates to the underlying table can continue during the ALTER
>INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled
>index or an index with ALLOW_PAGE_LOCKS set to OFF.
>-and-
>The rebuild operation can be minimally logged if the database recovery model
>is set to either bulk-logged or simple. For more information, see Choosing a
>Recovery Model for Index Operations.
>--
>and rebuilding one index at a time, not using the ALL option.
>I'm not sure how the logging change will affect memory, but it could easily
>help. One thing is for sure, your .ldf file won't bloat.
>Reorganize is the option I always go for first when dealing with indexes.
>It's lower impact overall and will frequently do the job. There might even
>be more options that can help you in there, but I can't tell you because I
>don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
>DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
>tell you the fragmentation within the index.
>Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
>indexes where the defrag will do no good.
>On to current answers.
>> When I say "Working Set" I am referring to SQL Servers Working Set, not
>> the
>> perfmon counter. This is the error in my SQL Error Log that I posted at
>> the
>> beginning of this thread:
>It is the same thing. The Working Set (for the 3rd time) is the TOTAL demand
>for memory. Your total memory is RAM + (Page File size - RAM). If you go
>over the RAM you have available, you start to page.
>Please read the following link. It is a short article and very good.
>http://support.microsoft.com/kb/555223
>> And while I believe John's suggestion may be the solution, I really need
>> to
>> be able to explain it to my team, before I just make a wild configuration
>> change.
>His suggestion was to reduce SQL Server's MAX memory setting so that it
>didn't ask for as much from Windows. Hardly a "wild configuration change".
>> Which gets back to my latest inquiry:
>> As far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>The SPID you are looking at is in SQL Server and therefore controlled by the
>server's settings.
>> And you are saying that reducing the amount SQL Server is asking for, will
>> in
>> turn not deplete the Working Set any more than it is currently? Does this
>> mean that reducing the amount SQL Server is asking for (reducing from 14GB
>> to
>> 12Gb) will make the Working Set larger?
>Sigh. No, it will not make the working set larger, it will make it smaller -
>which is what you want. You seem to think the Working Set is the pool of
>memory you are drawing from, it is not. The Working Set is drawing from
>system memory.
>>What do you mean when you say "Working Set"?
>[quoted text clipped - 21 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via http://www.sqlmonster.com|||Before going on (and to continue) I require answers to the following:
1) Why are you doing a reindex? What told you you needed it?
2) Syntax specifically how are you doing it?
3) Why have you completly ignored the posibility of using indexdefrag, or
changing the Recovery model while you reindex?
Please post both your reasons and the ALTER INDEX statement (you may, of
course change the database/table/index names, if you feel those shouldn't be
posted).
> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
> and
> the Paging File.
> I am still not understanding some things.
Did you study the link?
> So the Working Set is drawing from system memory, and when you say system
> memory I interpret that as being RAM + Paging File.
correct.
> In decreasing the Max Server memory I therefore decrease the Working Set
> dedicated to SQL Server.
Decreasing Max Server memory will decrease the Working Set. However, since
the Working Set is a TOTAL for Windows, we neither know, or care what is
dedicated to SQL Server.
> From the following two error messages I read that the sql server process
> is
> utilizing (Max Server or Working Set) memory 0% and 12% respectively.
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 93180, committed (KB): 15052932, memory utilization: 0%.
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 328 seconds. Working
> set
> (KB): 609700, committed (KB): 4931236, memory utilization: 12%.
> My interpretation to my issue of not being able to reindex, is that this
> reindexing job exceeds the amount of RAM available and the OS is moving
> pages
> out of SQL Servers Working Set (comparing the (KB) in the the error log
> entries above for the Working Set). If in setting the Max Server memory to
> a
> lower setting decreases the Working Set, and the error log shows the
> Working
> Set as suffering already when under stress, I do not see how lowering Max
> Server memory will help.
Because, decreasing the Max Server memory will reduce the amount of used
memory on the system, thus making more available for other tasks.
AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
> Please clarify.
>
> Jay wrote:
>>cbrichards,
>>I decided to answer your last post first (below) and then look at the
>>problem from the beginning. You don't really care about Working Sets,
>>paging
>>and max memory, you just want to re-index a table.
>>If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
>>will see it is a rich and fully featured command (I was amazed when I
>>first
>>read it). There are ways you can make it less intrusive to your system.
>>At the top of my list would be the following three options:
>>From BOL:
>>--
>> REORGANIZE
>> Specifies the index leaf level will be reorganized. This clause is
>>equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
>>performed online. This means long-term blocking table locks are not held
>>and
>>queries or updates to the underlying table can continue during the ALTER
>>INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a
>>disabled
>>index or an index with ALLOW_PAGE_LOCKS set to OFF.
>>-and-
>>The rebuild operation can be minimally logged if the database recovery
>>model
>>is set to either bulk-logged or simple. For more information, see Choosing
>>a
>>Recovery Model for Index Operations.
>>--
>>and rebuilding one index at a time, not using the ALL option.
>>I'm not sure how the logging change will affect memory, but it could
>>easily
>>help. One thing is for sure, your .ldf file won't bloat.
>>Reorganize is the option I always go for first when dealing with indexes.
>>It's lower impact overall and will frequently do the job. There might even
>>be more options that can help you in there, but I can't tell you because I
>>don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
>>DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
>>tell you the fragmentation within the index.
>>Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
>>indexes where the defrag will do no good.
>>On to current answers.
>> When I say "Working Set" I am referring to SQL Servers Working Set, not
>> the
>> perfmon counter. This is the error in my SQL Error Log that I posted at
>> the
>> beginning of this thread:
>>It is the same thing. The Working Set (for the 3rd time) is the TOTAL
>>demand
>>for memory. Your total memory is RAM + (Page File size - RAM). If you go
>>over the RAM you have available, you start to page.
>>Please read the following link. It is a short article and very good.
>>http://support.microsoft.com/kb/555223
>> And while I believe John's suggestion may be the solution, I really need
>> to
>> be able to explain it to my team, before I just make a wild
>> configuration
>> change.
>>His suggestion was to reduce SQL Server's MAX memory setting so that it
>>didn't ask for as much from Windows. Hardly a "wild configuration change".
>> Which gets back to my latest inquiry:
>> As far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>>The SPID you are looking at is in SQL Server and therefore controlled by
>>the
>>server's settings.
>> And you are saying that reducing the amount SQL Server is asking for,
>> will
>> in
>> turn not deplete the Working Set any more than it is currently? Does
>> this
>> mean that reducing the amount SQL Server is asking for (reducing from
>> 14GB
>> to
>> 12Gb) will make the Working Set larger?
>>Sigh. No, it will not make the working set larger, it will make it
>>smaller -
>>which is what you want. You seem to think the Working Set is the pool of
>>memory you are drawing from, it is not. The Working Set is drawing from
>>system memory.
>>What do you mean when you say "Working Set"?
>>[quoted text clipped - 21 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take
>>> >> >> place?
> --
> Message posted via http://www.sqlmonster.com
>|||Before I answer the questions you posed, let me say that it is not just
reindexing that is cratering the server, but queries in general on the server
itself. These queries would not crater the system (I am assuming) if we could
get the tables they referenced, reindexed. But in trying to reindex, the
reindex craters the server. So either way, we are up against a wall.
1a. Why am I doing a reindex?
For the reason most people want to reindex, to get the data they are asking
for returned in a timely and efficient manner.
1b. What told you you needed it?
A. The queries themself taking a long time to return results and at times
degrading overall server performance. Which in turn pointed to index
fragmentation.
B. The following query (when we were lucky enough to get results) confirmed
our suspicions that our indexes were greatly fragmented:
SELECT s.name,
i.name,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
JOIN master.sys.dm_db_index_physical_stats (' + CAST(@.DBID as varchar(max)
) + ', NULL, NULL , NULL, 'LIMITED') ps
ON s.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE s.type_desc = 'USER_TABLE'
AND i.index_id > 0
AND i.index_id < 255
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ps.Page_Count >= 100
AND (ps.avg_fragmentation_in_percent >= 5.0
OR ps.fragment_count >= 50)
GROUP BY s.name,
i.name,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count
2. Syntax specifically how are you doing it?
ALTER INDEX ' + @.IndexName + ' ON [' + @.DBName + '].[dbo].[' + @.TableName + ']
REBUILD WITH ( FILLFACTOR = ' + CAST(@.FillFactor AS varchar(3)) + ',
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF )
3. Why have you completly [sic] ignored the posibility [sic] of using
indexdefrag, or changing the Recovery model while you reindex?
I have not ignored the possibility of using indexdefrag. We might give it a
try once we implement new Max/Min Server memory settings. You might say we
are a bit gun shy of making these changes without understanding all the
implications, and additionally, since any resource intense operation seems to
greatly degrade performance, I am a bit hesitant in performing an indexdefrag,
too.
Lastly, the Recovery model on the database is already set to SIMPLE.
Jay wrote:
>Before going on (and to continue) I require answers to the following:
>1) Why are you doing a reindex? What told you you needed it?
>2) Syntax specifically how are you doing it?
>3) Why have you completly ignored the posibility of using indexdefrag, or
>changing the Recovery model while you reindex?
>Please post both your reasons and the ALTER INDEX statement (you may, of
>course change the database/table/index names, if you feel those shouldn't be
>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say system
>> memory I interpret that as being RAM + Paging File.
>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>Decreasing Max Server memory will decrease the Working Set. However, since
>the Working Set is a TOTAL for Windows, we neither know, or care what is
>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering Max
>> Server memory will help.
>Because, decreasing the Max Server memory will reduce the amount of used
>memory on the system, thus making more available for other tasks.
>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hmm, thank you sir. Three follow up questions:
Have you tried recompiling any procedures to see if it improves performance?
(see sp_recompile in BOL)
Is the drive itself fragmented?
Have you used the Profiler to verify effecient query plans?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79d74e1593f56@.uwe...
> Before I answer the questions you posed, let me say that it is not just
> reindexing that is cratering the server, but queries in general on the
> server
> itself. These queries would not crater the system (I am assuming) if we
> could
> get the tables they referenced, reindexed. But in trying to reindex, the
> reindex craters the server. So either way, we are up against a wall.
> 1a. Why am I doing a reindex?
> For the reason most people want to reindex, to get the data they are
> asking
> for returned in a timely and efficient manner.
> 1b. What told you you needed it?
> A. The queries themself taking a long time to return results and at times
> degrading overall server performance. Which in turn pointed to index
> fragmentation.
> B. The following query (when we were lucky enough to get results)
> confirmed
> our suspicions that our indexes were greatly fragmented:
> SELECT s.name,
> i.name,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (' + CAST(@.DBID as
> varchar(max)
> ) + ', NULL, NULL , NULL, 'LIMITED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.Page_Count >= 100
> AND (ps.avg_fragmentation_in_percent >= 5.0
> OR ps.fragment_count >= 50)
> GROUP BY s.name,
> i.name,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count
> 2. Syntax specifically how are you doing it?
> ALTER INDEX ' + @.IndexName + ' ON [' + @.DBName + '].[dbo].[' + @.TableName
> + ']
> REBUILD WITH ( FILLFACTOR = ' + CAST(@.FillFactor AS varchar(3)) + ',
> SORT_IN_TEMPDB = ON,
> STATISTICS_NORECOMPUTE = OFF )
> 3. Why have you completly [sic] ignored the posibility [sic] of using
> indexdefrag, or changing the Recovery model while you reindex?
> I have not ignored the possibility of using indexdefrag. We might give it
> a
> try once we implement new Max/Min Server memory settings. You might say we
> are a bit gun shy of making these changes without understanding all the
> implications, and additionally, since any resource intense operation seems
> to
> greatly degrade performance, I am a bit hesitant in performing an
> indexdefrag,
> too.
> Lastly, the Recovery model on the database is already set to SIMPLE.
> Jay wrote:
>>Before going on (and to continue) I require answers to the following:
>>1) Why are you doing a reindex? What told you you needed it?
>>2) Syntax specifically how are you doing it?
>>3) Why have you completly ignored the posibility of using indexdefrag, or
>>changing the Recovery model while you reindex?
>>Please post both your reasons and the ALTER INDEX statement (you may, of
>>course change the database/table/index names, if you feel those shouldn't
>>be
>>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual
>> Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say
>> system
>> memory I interpret that as being RAM + Paging File.
>>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>>Decreasing Max Server memory will decrease the Working Set. However, since
>>the Working Set is a TOTAL for Windows, we neither know, or care what is
>>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering
>> Max
>> Server memory will help.
>>Because, decreasing the Max Server memory will reduce the amount of used
>>memory on the system, thus making more available for other tasks.
>>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks again Jay. Your tolerance on this matter is appreciated. Getting to
your questions:
1. Have you tried recompiling any procedures to see if it improves
performance?
(see sp_recompile in BOL)
No, this has not been attempted. The tables are mostly archive data, and for
now, we mostly compose ad-hoc queries to mine the data when we need it.
Is the drive itself fragmented?
I do not believe it is very fragmented, as the server was newly built as 64
bit, and has only been in operation for 30 days. Our files are set to auto
grow, but they have not grown since they were created, as we grew them to
begin with, with ample room to start.
Have you used the Profiler to verify effecient query plans?
No, I did not put a trace on my reindex attempt, and the other ad-hoc query
that greatly degraded performance I did not either. I would be interested in
the query plan output, but the degredation after converting to 64 bit has me
focusing my time more on understanding the nuances of 64 bit versus 32 bit.
Jay wrote:
>Hmm, thank you sir. Three follow up questions:
>Have you tried recompiling any procedures to see if it improves performance?
>(see sp_recompile in BOL)
>Is the drive itself fragmented?
>Have you used the Profiler to verify effecient query plans?
>> Before I answer the questions you posed, let me say that it is not just
>> reindexing that is cratering the server, but queries in general on the
>[quoted text clipped - 121 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Jay,
You are probably quite put out with my understanding of the Working Set, and
understandably so. My thinking that the SQL Server has it's own Working Set
came from the following blog:
http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
The second paragraph reads:
"First - let's define what exactly "Working Set" is. The working set of a
program is a collection of those pages in its virtual address space that have
been recently referenced. This includes both shared and private data. The
shared data includes pages that contain all instructions an application
executes, including those in its own DLL's and the system DLL's. As the
working set size increases, memory demand increases. A process has an
associated minimum working set size and maximum working set size. Each time
a process is created, it reserves the minimum working set size for the
process. The virtual memory manager attempts to keep enough memory for the
minimum working set resident when the process is active, but keeps no more
than the maximum size."
The following statements from the above paragraph led me to believe there was
a Working Set for each process, rather than a single Working Set shared by
all processes, which I believe you have stated. Nevertheless, the following
clips from the above paragraph led me to believe that SQL Server has its own
Working Set:
1. "The working set of a program"
2. "Each time a process is created, it reserves the minimum working set size
for the process."
There are other references in the article that support multiple Working Sets,
or a Working Set per process, such as: "If available server memory drops
below 100MB, then the Memory Manager will trim the Working Set of all
processes."
You may slap me alongside the head with a wet fish, if you please. I am just
confused and seeking for better understanding as to how, setting the Min/Max
Memory server settings to the same value and reducing the Max Memory Server
setting will help reduce the following error log entries:
"A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%."
Thanks again for sharing your knowledge and tolerating my ignorance.
Jay wrote:
>Before going on (and to continue) I require answers to the following:
>1) Why are you doing a reindex? What told you you needed it?
>2) Syntax specifically how are you doing it?
>3) Why have you completly ignored the posibility of using indexdefrag, or
>changing the Recovery model while you reindex?
>Please post both your reasons and the ALTER INDEX statement (you may, of
>course change the database/table/index names, if you feel those shouldn't be
>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say system
>> memory I interpret that as being RAM + Paging File.
>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>Decreasing Max Server memory will decrease the Working Set. However, since
>the Working Set is a TOTAL for Windows, we neither know, or care what is
>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering Max
>> Server memory will help.
>Because, decreasing the Max Server memory will reduce the amount of used
>memory on the system, thus making more available for other tasks.
>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via http://www.sqlmonster.com|||The quote from the blog is correct and led to a unfortunate
misunderstanding.
In the second paragraph it says:
"As the working set size increases, memory demand increases."
Meaning that it is not a fixed resource, but variable to an outside pool.
Beyond that, it does indeed talk about the working set for seperate
processes, which makes sense. Its just that when speaking about paging and
running out of system memory, it is generally assumed that you're looking at
the total in Windows.
Actually, after reading the link, if I knew about it before hand, I would
have sent it to you as it supports John's suggestion.
However, I'm not so sure that reindexing will solve your problem. I think
it's in the query plan, but not sure exactly why (of if) it is an issue.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79d8a46a06185@.uwe...
> Jay,
> You are probably quite put out with my understanding of the Working Set,
> and
> understandably so. My thinking that the SQL Server has it's own Working
> Set
> came from the following blog:
> http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
>
> The second paragraph reads:
> "First - let's define what exactly "Working Set" is. The working set of a
> program is a collection of those pages in its virtual address space that
> have
> been recently referenced. This includes both shared and private data.
> The
> shared data includes pages that contain all instructions an application
> executes, including those in its own DLL's and the system DLL's. As the
> working set size increases, memory demand increases. A process has an
> associated minimum working set size and maximum working set size. Each
> time
> a process is created, it reserves the minimum working set size for the
> process. The virtual memory manager attempts to keep enough memory for
> the
> minimum working set resident when the process is active, but keeps no more
> than the maximum size."
> The following statements from the above paragraph led me to believe there
> was
> a Working Set for each process, rather than a single Working Set shared by
> all processes, which I believe you have stated. Nevertheless, the
> following
> clips from the above paragraph led me to believe that SQL Server has its
> own
> Working Set:
> 1. "The working set of a program"
> 2. "Each time a process is created, it reserves the minimum working set
> size
> for the process."
> There are other references in the article that support multiple Working
> Sets,
> or a Working Set per process, such as: "If available server memory drops
> below 100MB, then the Memory Manager will trim the Working Set of all
> processes."
> You may slap me alongside the head with a wet fish, if you please. I am
> just
> confused and seeking for better understanding as to how, setting the
> Min/Max
> Memory server settings to the same value and reducing the Max Memory
> Server
> setting will help reduce the following error log entries:
> "A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%."
> Thanks again for sharing your knowledge and tolerating my ignorance.
>
> Jay wrote:
>>Before going on (and to continue) I require answers to the following:
>>1) Why are you doing a reindex? What told you you needed it?
>>2) Syntax specifically how are you doing it?
>>3) Why have you completly ignored the posibility of using indexdefrag, or
>>changing the Recovery model while you reindex?
>>Please post both your reasons and the ALTER INDEX statement (you may, of
>>course change the database/table/index names, if you feel those shouldn't
>>be
>>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual
>> Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say
>> system
>> memory I interpret that as being RAM + Paging File.
>>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>>Decreasing Max Server memory will decrease the Working Set. However, since
>>the Working Set is a TOTAL for Windows, we neither know, or care what is
>>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering
>> Max
>> Server memory will help.
>>Because, decreasing the Max Server memory will reduce the amount of used
>>memory on the system, thus making more available for other tasks.
>>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi cbrichards,
Any movement?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79bdf69e9af82@.uwe...
>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
> 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Jay,
Check this article out:
918483 How to reduce paging of buffer pool memory in the 64-bit version of
SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
There are also some drivers that are known to cause this issue, like the the
iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard
is known to cause this issue on x64 editions of SQL Server 2005.
Also some of the Broadcom drivers , so apart from setting the locked pages
in memory option to on, I would also make sure you are running the latest
drivers for your hardware and software if possible.
HTH
"Jay" <nospan@.nospam.org> wrote in message
news:%23dipTOMFIHA.3716@.TK2MSFTNGP03.phx.gbl...
> Hi cbrichards,
> Any movement?
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:79bdf69e9af82@.uwe...
>>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>>Min
>> Server Memory setting is 0, and Max is set at 14 GB.
>> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
>> 6GB
>> in size, the server grinds to a halt and the error log contains errors
>> similar to the following:
>> A significant part of sql server process memory has been paged out. This
>> may result in a performance degradation. Duration: 655 seconds. Working
>> set
>> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
>> Reporting Services is also running on this box, but when the Reindex is
>> taking place, the Reindex job is the only active SPID.
>> The "PF Usage" in Task Manager is at 15.3 GB.
>> Any idea what one can do to allow this Reindex to take place?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>

Friday, March 30, 2012

Relatively easy SQL SELECT statement issues :/

Wednesday, March 28, 2012

Relationships getting deleted (MS SQL)

Relationship Problem

Friday, March 23, 2012

related rooms query

Related Records Inport from single CSV

Related Records Inport from single CSV

Related Records Inport from single CSV

Reinstalling SQL Server on a Cluster

Reinstalling SQL Server on a Cluster

Reinstalling SQL Server on a Cluster

Tuesday, March 20, 2012

re-insert / templating records

Reinitializing subscriptions not working

Friday, March 9, 2012

Reindex depleting Working Set

Registering Error

Blog Archive

About Me