I'm trying to create a Cube on analysis services, but do not see any
realtions in the diagram between my selected tables. The tables use indexes
rather than primary keys. Do you have any suggestions about how I can specify
the relations from Analysis Services.
Thanksdo you talk about the DSV (data source view) schema?
the system can detect relationships based on column names, you have an
option which allow you to change the way visual studio will detect the
relationship between the tables.
but this works BEFORE you add tables in the DSV
after this you have to manually create the links in the DSV. (its a simple
drag&drop)
"dbach" <dbach@.discussions.microsoft.com> wrote in message
news:E0F20189-17FC-443D-8348-2CC63172F0FD@.microsoft.com...
> I'm trying to create a Cube on analysis services, but do not see any
> realtions in the diagram between my selected tables. The tables use
> indexes
> rather than primary keys. Do you have any suggestions about how I can
> specify
> the relations from Analysis Services.
> Thanks
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
analysis manager question - cube
Hi!
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to
be build.Create the views with the same level of data grain and build dimen
sions.
Once the dimensions and facts table are joined together, process the cube wi
th the "Full Process" option. For the new records to be appeared in the cube
, go for "Incremental update" or "Refresh Cube".
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to
be build.Create the views with the same level of data grain and build dimen
sions.
Once the dimensions and facts table are joined together, process the cube wi
th the "Full Process" option. For the new records to be appeared in the cube
, go for "Incremental update" or "Refresh Cube".
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
analysis manager question - cube
Hi!
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!
Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to be build.Create the views with the same level of data grain and build dimensions.
Once the dimensions and facts table are joined together, process the cube with the "Full Process" option. For the new records to be appeared in the cube, go for "Incremental update" or "Refresh Cube".
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!
Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to be build.Create the views with the same level of data grain and build dimensions.
Once the dimensions and facts table are joined together, process the cube with the "Full Process" option. For the new records to be appeared in the cube, go for "Incremental update" or "Refresh Cube".
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Tuesday, March 20, 2012
An ugly self-join (oh, that doesnt work!)
Hi again, all...here I am again, trying to work on my CloseIndex thang again...Same subject, different tack...
Basically, I have two tables...for the sake of simplicity, let me define them as:
PortfolioIndex
PortfolioID int
CreateDate smalldatetime
CloseIndex float
PortfolioPerformance
PortfolioID int
CreateDate smalldatetime
PrevDate smalldatetime
DailyPerChg float
UPDATE PortfolioIndex
SET CloseIndex = CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
WHERE (P.PortfolioID = PP.PortfolioID) AND
((P.CreateDate = PP.CreateDate) AND
(P.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))
What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.
As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).
How can I make this work (and if possible, make it prettier too! *L* ;) )well, now I see it doesn't really work in the sql analyzer either...but did on a previous iteration (before I added the inner join).
Still, the idea/question is the same...is there a less kludg-ey way to do the self-join to get the previous day's row and update the new row based on data from the old and the 2nd (PortfolioPerformance) table?
Thanks,
Paul|||select /*PI.CloseIndex = */ PI.PortfolioID, CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PI, PortfolioIndex PPI, PortfolioPerformance PP
where ((PI.PortfolioID = PPI.PortfolioID AND
PI.PortfolioID = PP.PortfolioID))AND
((PI.CreateDate = PP.CreateDate) AND
(PI.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))
It worked this way in the SQL Analyzer...when I was testing without the update...so...how can I reference the table in an update without being ambiguous? I can't seem to find any example code anywhere about updates with self-joins...can it be done?
Thanks,
Paul|||Update table
set column = (select ...)|||Thanks...*hanging head*
It must be time to go home...
Thanks for taking the time to point me in the direction of the forest once again...|||I've been trying to get the select correct as suggested, but can't get it to work for me.
Possibly because one requirement is not plain from my previous description...
There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).
Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*
Here is my modified query:
DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'
Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))
The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Any helpful suggestions?|||anyone? I know I must be missing something obvious, but can't see it...
how does one update a table joined to itself in the select clause?
Originally posted by TallCowboy0614
I've been trying to get the select correct as suggested, but can't get it to work for me.
Possibly because one requirement is not plain from my previous description...
There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).
Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*
Here is my modified query:
DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'
Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))
The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Any helpful suggestions?|||The error say it all...how can you update a column with n results...you need to make sure the query only returns 1
per row
Forget the update and just focus on the select to make sure it's returning what you need.sql
Basically, I have two tables...for the sake of simplicity, let me define them as:
PortfolioIndex
PortfolioID int
CreateDate smalldatetime
CloseIndex float
PortfolioPerformance
PortfolioID int
CreateDate smalldatetime
PrevDate smalldatetime
DailyPerChg float
UPDATE PortfolioIndex
SET CloseIndex = CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
WHERE (P.PortfolioID = PP.PortfolioID) AND
((P.CreateDate = PP.CreateDate) AND
(P.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))
What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.
As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).
How can I make this work (and if possible, make it prettier too! *L* ;) )well, now I see it doesn't really work in the sql analyzer either...but did on a previous iteration (before I added the inner join).
Still, the idea/question is the same...is there a less kludg-ey way to do the self-join to get the previous day's row and update the new row based on data from the old and the 2nd (PortfolioPerformance) table?
Thanks,
Paul|||select /*PI.CloseIndex = */ PI.PortfolioID, CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PI, PortfolioIndex PPI, PortfolioPerformance PP
where ((PI.PortfolioID = PPI.PortfolioID AND
PI.PortfolioID = PP.PortfolioID))AND
((PI.CreateDate = PP.CreateDate) AND
(PI.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))
It worked this way in the SQL Analyzer...when I was testing without the update...so...how can I reference the table in an update without being ambiguous? I can't seem to find any example code anywhere about updates with self-joins...can it be done?
Thanks,
Paul|||Update table
set column = (select ...)|||Thanks...*hanging head*
It must be time to go home...
Thanks for taking the time to point me in the direction of the forest once again...|||I've been trying to get the select correct as suggested, but can't get it to work for me.
Possibly because one requirement is not plain from my previous description...
There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).
Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*
Here is my modified query:
DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'
Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))
The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Any helpful suggestions?|||anyone? I know I must be missing something obvious, but can't see it...
how does one update a table joined to itself in the select clause?
Originally posted by TallCowboy0614
I've been trying to get the select correct as suggested, but can't get it to work for me.
Possibly because one requirement is not plain from my previous description...
There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).
Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*
Here is my modified query:
DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'
Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))
The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Any helpful suggestions?|||The error say it all...how can you update a column with n results...you need to make sure the query only returns 1
per row
Forget the update and just focus on the select to make sure it's returning what you need.sql
An other DB copy Q:
Hello,
In an instance, I've two DBs: ABC y copyABC. I would like to update the
tables (and their contents added or removed) from ABC to copyABC twice a
day. For it will be using a Job but, what would be the syntax?
The idea is to get data for my reports from copyABC and creating my own
queries without disrupting the daily usage of ABC.
Would appreciate some directions.
Thank you
MarySSIS script or BulkCopy or SqlBulkCopy method. You might also consider
ADO.NET 3.5 Sync Services.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Mary" <noemail@.hotmail.com> wrote in message
news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
> Hello,
> In an instance, I've two DBs: ABC y copyABC. I would like to update the
> tables (and their contents added or removed) from ABC to copyABC twice a
> day. For it will be using a Job but, what would be the syntax?
> The idea is to get data for my reports from copyABC and creating my own
> queries without disrupting the daily usage of ABC.
> Would appreciate some directions.
> Thank you
> Mary
>|||Can a query be used? is so, how?
"William Vaughn [MVP]" <billvaNoSPAM@.betav.com> escribió en el mensaje de
noticias news:AAC71D98-1B95-4F63-90F4-0607B79F1222@.microsoft.com...
> SSIS script or BulkCopy or SqlBulkCopy method. You might also consider
> ADO.NET 3.5 Sync Services.
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
> "Mary" <noemail@.hotmail.com> wrote in message
> news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
>> Hello,
>> In an instance, I've two DBs: ABC y copyABC. I would like to update the
>> tables (and their contents added or removed) from ABC to copyABC twice a
>> day. For it will be using a Job but, what would be the syntax?
>> The idea is to get data for my reports from copyABC and creating my own
>> queries without disrupting the daily usage of ABC.
>> Would appreciate some directions.
>> Thank you
>> Mary
>|||SSIS (as launched via SQL Server Management Services) can support a query to
specify which rows to import/export
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Mary" <noemail@.hotmail.com> wrote in message
news:72185CFE-FB07-4E0C-8255-1FC84A034C8E@.microsoft.com...
> Can a query be used? is so, how?
> "William Vaughn [MVP]" <billvaNoSPAM@.betav.com> escribió en el mensaje de
> noticias news:AAC71D98-1B95-4F63-90F4-0607B79F1222@.microsoft.com...
>> SSIS script or BulkCopy or SqlBulkCopy method. You might also consider
>> ADO.NET 3.5 Sync Services.
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>> "Mary" <noemail@.hotmail.com> wrote in message
>> news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
>> Hello,
>> In an instance, I've two DBs: ABC y copyABC. I would like to update the
>> tables (and their contents added or removed) from ABC to copyABC twice a
>> day. For it will be using a Job but, what would be the syntax?
>> The idea is to get data for my reports from copyABC and creating my own
>> queries without disrupting the daily usage of ABC.
>> Would appreciate some directions.
>> Thank you
>> Mary
>>
>
In an instance, I've two DBs: ABC y copyABC. I would like to update the
tables (and their contents added or removed) from ABC to copyABC twice a
day. For it will be using a Job but, what would be the syntax?
The idea is to get data for my reports from copyABC and creating my own
queries without disrupting the daily usage of ABC.
Would appreciate some directions.
Thank you
MarySSIS script or BulkCopy or SqlBulkCopy method. You might also consider
ADO.NET 3.5 Sync Services.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Mary" <noemail@.hotmail.com> wrote in message
news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
> Hello,
> In an instance, I've two DBs: ABC y copyABC. I would like to update the
> tables (and their contents added or removed) from ABC to copyABC twice a
> day. For it will be using a Job but, what would be the syntax?
> The idea is to get data for my reports from copyABC and creating my own
> queries without disrupting the daily usage of ABC.
> Would appreciate some directions.
> Thank you
> Mary
>|||Can a query be used? is so, how?
"William Vaughn [MVP]" <billvaNoSPAM@.betav.com> escribió en el mensaje de
noticias news:AAC71D98-1B95-4F63-90F4-0607B79F1222@.microsoft.com...
> SSIS script or BulkCopy or SqlBulkCopy method. You might also consider
> ADO.NET 3.5 Sync Services.
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
> "Mary" <noemail@.hotmail.com> wrote in message
> news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
>> Hello,
>> In an instance, I've two DBs: ABC y copyABC. I would like to update the
>> tables (and their contents added or removed) from ABC to copyABC twice a
>> day. For it will be using a Job but, what would be the syntax?
>> The idea is to get data for my reports from copyABC and creating my own
>> queries without disrupting the daily usage of ABC.
>> Would appreciate some directions.
>> Thank you
>> Mary
>|||SSIS (as launched via SQL Server Management Services) can support a query to
specify which rows to import/export
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Mary" <noemail@.hotmail.com> wrote in message
news:72185CFE-FB07-4E0C-8255-1FC84A034C8E@.microsoft.com...
> Can a query be used? is so, how?
> "William Vaughn [MVP]" <billvaNoSPAM@.betav.com> escribió en el mensaje de
> noticias news:AAC71D98-1B95-4F63-90F4-0607B79F1222@.microsoft.com...
>> SSIS script or BulkCopy or SqlBulkCopy method. You might also consider
>> ADO.NET 3.5 Sync Services.
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>> "Mary" <noemail@.hotmail.com> wrote in message
>> news:C17402E7-3501-44B5-9D2F-7D03FA01B161@.microsoft.com...
>> Hello,
>> In an instance, I've two DBs: ABC y copyABC. I would like to update the
>> tables (and their contents added or removed) from ABC to copyABC twice a
>> day. For it will be using a Job but, what would be the syntax?
>> The idea is to get data for my reports from copyABC and creating my own
>> queries without disrupting the daily usage of ABC.
>> Would appreciate some directions.
>> Thank you
>> Mary
>>
>
An odd quandry.
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.
object link embedding procedure.
Basically importing filenames.
The three fields in question are identical.
Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value
And these three fields are populated from a substring of the filenames
which are generated in another table.
Filenametbl.pickno
here is the rub.
If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)
should return 200 records.
It returns 0 records.
So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)
returns 200 records in this scenario,
this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)
I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))
Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
WHERE ... NOT IN.
select
*
from
Filenametbl f
where not exists (select * from
document_link_staging dls
where dls.key2_value = f.pickno)
and not exists (select * from
document_link_storage dls
where dls.key2_value = f.pickno)
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.
The three fields in question are identical.
Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value
And these three fields are populated from a substring of the filenames
which are generated in another table.
Filenametbl.pickno
here is the rub.
If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)
should return 200 records.
It returns 0 records.
So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)
returns 200 records in this scenario,
this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)
I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))
Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.|||Hey, whoa, that works.
You're the man Tom.
So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?
Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.|||(KyussWren@.gmail.com) writes:
> Hey, whoa, that works.
> You're the man Tom.
> So just use EXISTS when there are nulls in the select list?
> Kinda like coalesce, but for subqueries?
EXISTS and NOT EXISTS have wider applicability than so. You also need
EXISTS / NOT EXISTS when the condition involves more than one column.
IN + subquery is mainly something I use when I'm writing ad hoc-queries
and I'm lazy. In programming code I use EXISTS / NOT EXISTS 90% of
the time.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm a big EXISTS fan. The NULLs here are to do with the column you chose in
your subquery. I wouldn't think of it like COALESCE. Basically, NULL <>
anything, even another NULL. An IN predicate can be broken down like this:
x IN (1, 2, 3 null)
... means:
x = 1 or x = 2 or x = 3 or x = null
So, if x is 1, 2 or 3, it will be true. If x is null, then the result is
false, since x is really unknown and not equal to anything.
Now consider this:
x NOT IN (1, 2, 3 null)
... means:
x <> 1 and x <> 2 and x <> 3 and x <> null
Google de Morgan's Law.
What if x is 4? All conditions must be met. It passes the first 3, but
fails on the last, since 4 <> null is unknown, and is treated as false.
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146665188.587472.63170@.v46g2000cwv.googlegro ups.com...
Hey, whoa, that works.
You're the man Tom.
So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?
Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead
> of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.
Monday, March 19, 2012
An interview question
Hi,
My wife had an interview today. Below is the sql-question she was asked:
There are 2 tables, A and B. The tables have same structure - each of them consists of 100 columns named c_1 through c_100. There are no primary keys defined on the tables.
Each table has 10,000 rows. Most rows are identical for tables A and B, however there are few that are not.
She was required to show only those rows that have no match in the other table - by 1 query that will be short, i.e. - will not contain someting like "col_1,col_2,.._col_99,Col_100" .Hmm...interesting question. It's hard to think of how to do this if there is no field(s) that can be used to join the two tables together, but part of the way might be doing a UNION on the two tables since they have the same structure, then using the UNION as a sub-query for an outer GROUP BY and do a HAVING Count(*) = 1. That way, it'll only show records that do not have a match to another table.
The problem with this is that you don't know which table contains that record and you do have to do the GROUP BY on all the fields outputted from the UNION query.
I'm kinda reaching here, but maybe create some type of primary key composed of the data in each row...by creating an expression like SOUNDEX(field1 + field2 + ... field99 + field100). That way, you have a primary key that might be dependable for use in an OUTER or FULL join. Just another idea...
Maybe something like this could be part of the way...I'm curious as to what the answer would be myself, especially if the answer has to be "1 query that will be short".
Kael V. Dowdy MCSD, MCP|||I think the answer involves using the BINARY_CHECKSUM function, but I'll have to check it out.
blindman|||OK, here goes:
select *
from (select BINARY_CHECKSUM(*) CheckSum, * from TableA) TableA
full outer join (select BINARY_CHECKSUM(*) CheckSum, * from TableB) TableB
on TableA.CHeckSum = TableB.Checksum
where TableA.Checksum is null or TableB.Checksum is null
The full outer join should show any differences between the two tables.
My wife had an interview today. Below is the sql-question she was asked:
There are 2 tables, A and B. The tables have same structure - each of them consists of 100 columns named c_1 through c_100. There are no primary keys defined on the tables.
Each table has 10,000 rows. Most rows are identical for tables A and B, however there are few that are not.
She was required to show only those rows that have no match in the other table - by 1 query that will be short, i.e. - will not contain someting like "col_1,col_2,.._col_99,Col_100" .Hmm...interesting question. It's hard to think of how to do this if there is no field(s) that can be used to join the two tables together, but part of the way might be doing a UNION on the two tables since they have the same structure, then using the UNION as a sub-query for an outer GROUP BY and do a HAVING Count(*) = 1. That way, it'll only show records that do not have a match to another table.
The problem with this is that you don't know which table contains that record and you do have to do the GROUP BY on all the fields outputted from the UNION query.
I'm kinda reaching here, but maybe create some type of primary key composed of the data in each row...by creating an expression like SOUNDEX(field1 + field2 + ... field99 + field100). That way, you have a primary key that might be dependable for use in an OUTER or FULL join. Just another idea...
Maybe something like this could be part of the way...I'm curious as to what the answer would be myself, especially if the answer has to be "1 query that will be short".
Kael V. Dowdy MCSD, MCP|||I think the answer involves using the BINARY_CHECKSUM function, but I'll have to check it out.
blindman|||OK, here goes:
select *
from (select BINARY_CHECKSUM(*) CheckSum, * from TableA) TableA
full outer join (select BINARY_CHECKSUM(*) CheckSum, * from TableB) TableB
on TableA.CHeckSum = TableB.Checksum
where TableA.Checksum is null or TableB.Checksum is null
The full outer join should show any differences between the two tables.
An interesting issue with adding columns.
I've written this procedure that is a meta data manager if you will. It looks at a vocabulary table and will create or alter tables to be in line with the meta data. It works great and has for a couple of years now. However, I just rewrote it in an effort to make it more compact, efficient, etc. Now, our system uses at least three databases. One contains shared data, the next holds only system data for the app like individual user settings, etc (atslogin is the name) then would be an actual database for client data or accounts as we call them. Clients could have one or 100 of those. Please, no comments about moving everything in to one DB as that is not always possible to to hierarchy of multiple business models.
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)ooohhh dba-one, a padwon problem this is. meaningless the order of the data in the database is as Jedi Kaiser will readily remind you he will.|||Personally, some things are just not meant to be automated in my opinion
You'd have to post some code snipets though on what you are doing.
I use ERWin to do what you are doing, but I usually unload, drop and create the objects, then load|||Yoda say this ordinal problem not be a 0 to 1 issues. The voc file reads 0 for the first position while the system stuffs begins at 1. You didn't really think a Jedi master like me would be so stupid as to over look that did you?|||exec usp_madeof 'wcsub'
select colid,* from syscolumns where object_name(id)='wcsub' order by colid
Both will return the following in the results:
DOHFILING varchar 1 48
FORMTYPE varchar 2 53
(from sysobjects)
48 DOHFILING
53 FORMTYPE
Now, usp_made of is a procedure I wrote. It returns results in a fashion like Oracle's "desc" function. All that procedure does is read the INFORMATION_SCHEMA.[columns] view and order by ordinal position. Now, everytime I drop formtype and re add the column 53 will become 54 and so on. dohfiling will remain 48. This is nuts.|||Personally, some things are just not meant to be automated in my opinion
I very much agree. When I find columns out of order I refuse to automate the process. However, the columns are not out of order technically. I just can believe what syscolumns or other entries read. Even running alter table .. add column ... in the local DB as the owner is causing this so I can even remove my procedure from the matter.|||I tore out a couple hairs this week trying to get a Data Dictionary Collection script to work. Eventually, I just dumped the idea of checking columns by column ID and resorted to sorting the lower cased names of the columns and going by that. I figured if the column name exists, then I am OK. If it is not there at all, then I have to delete the column from the data dictionary. Or, if I find a column that is out of order, it must have been added, so in the data dictionary it goes. If I went by columnID I could be dropping good columns and re-adding them later on, when I find them again at the "end" of the table. Would that work for you?
EDIT: Bottom line is you have to work around the quirks in your RDBMS. And a non-contiguous set of column IDs counts as a quirk to me.|||Here read this.
http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
Once I read that, it only re-enforced dropping and re-creating the tables|||This has never happened and it is not an acceptable flaw of any RDBMS. Besides, my application expects things to be in a certain order and if it isn't, it will puke. I didn't design it that way but it is the way of things. If the table is created wrong then fine, manual intervention. If the system tables are wrong then how can I buy anything else they may tell me?|||This application is run all over the place at my clients sites. If it were an in house app I wouldn't mind dropping and recreating tables but that is horribly impractical in this case. Plus, I've had a few programmers here attempt to be clever and do things like that only to see it result in data loss. I can't attempt to automate the recreation of tables and moving data around. Too much risk for my taste.|||Personally, some things are just not meant to be automated in my opinionSo true. Experience is the difference between knowing what CAN be done, and what SHOULD be done. "Fail-safe systems fail by failing to fail safely."|||This application is run all over the place at my clients sites.
I don't understand, I would have a script that would run all the sql and apply the changes.
What kind of development lifecycle do you use?
And what's the frontt end written and how do you deploy that?|||The app is a VB/.Net app for insurance claims. It is a very complicated application as well. Scripts are fine in many cases but calls to my procedure are built in to it. When we make changes to table structures we simply send the meta data table. The app will then call my procedure to inspect and alter if needed, any involved tables. No single client has a standard version of our app because we will customize anything they want but the databases are largely the same with the exception of "user tables"
If we send an update that would say depend on a new table or two or many, we send the meta data file. The app will then test for existence and if they are not there my procedure creates the tables, index, keys, so on. Why send a meta data file and then have to manually do everything?
It seems everyone thinks I'm some kind of dummy for doing this! Our app has been doing things like this for nearly 20 years and the last 10 has been with Oracle or SQL Server depending on which DB the client wants to run it against. I just don't understand how this issue has only appeared now and not before.|||I can't be the only one who has run in to this can I?|||No single client has a standard version of our app because we will customize anything they want...and that is the root of the problem. Trying to be all things to all customers. I saw this as a problem back when I was a consumer of software, and I see it as a problem now that I am a producer of software. Client database get customized out the wazoo until they become administrative nightmares requiring the type of code you have had to implement (albeit for 20 years).
Software vendors should be experts in their industry and code their products according to best practices. Client modification of the database schema should not be allowed.
"You can customize it any way you want" is ultimately a flawed philosophy, and its reductio-ad-absurdum conclusion is an empty box with instructions to install the database server of the client's choice.|||You misunderstand. You may not tamper with the database but we will customize our programs. That is what I meant by that. Our Workers' Comp claim table is called WCCLM1 for example. Every client has this and if they get an update, they may need additional columns or columns expanded. if they add something to it, the app wont see it, etc
Oh and we don't ship a shrink wrapped product and tell them to plug it in to whatever they want. We support either Oracle or SQL Server only. We do a data conversion in most cases as well. We are hardly as stupid or sloppy as some may have understood.
Basically, client modifications to the database are not allowed and there is not a single thing that is arbitrary about the database design. Geez, all I need to do is add some columns or expand them without having to write a script every time. This isn't rocket science or reinvention of the wheel.|||Oh and one more thing. "You can customize it anyway you want" is true as long as you pay us to modify it. We don't ship code. We don't let clients ever have that, much less modify it. This isn't some open source trash. This is a 100k plus application.|||So you still run into the headache of maintaining code for as many versions as you have clients, but you have found a way to charge for the administrative hassle.
I'm not dissing your business plan. Whatever you and your clients agree to and makes you money is fair game. But I don't see how you can be surprised that rolling out updates in such a situation is a pain in the keister. It is difficult by design.|||Well, technically we only have two versions, 5.4 of our old system and 6.424 of out newer .NET version. Both use the same database, both use the same programs at any client site. What may be different is tables for a customized screen for a client claim program, Perhaps a custom AP, HR, etc interface. Our code and/or business model is not the mess some of you think it is. I guess I'm just not explaining it well.
Still, forget about all that. Even if it was a wreck, what does that have to do with what I consider a substantial flaw in this RDMS? Not a damn thing. The bottom line is that I should be able to add a column (oh and this happens even if I add the column manually and not via stored procedure in case anyone missed that) and have that recorded in the proper ordinal position in the system tables and views.
The feed back I'm getting here is that I'm stupid for expecting to actually utilize the alter table command. Someone explain why it's there if it can't be relied upon? You guys can talk all day long about how we distribute our app but no one can seem to offer anything of value with regard to the issue at hand.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!
Funny how I don't have this issue in Oracle. You don't even know what the hell you are talking about. Since when did I say what order they are in, inside the database matters to me? I never did. The application depends on things being a certain way. I didn't design that shit. Programmers did and now I have to deal with it. Still, how dare I expect things to be correct in SYSTEM TABLES! I'm not an idiot so I wish you people would stop assuming I am.
Thanks for nothing. All of you.|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.
I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?|||sweet Jesus the booty pleaser.
I bang this drum everywhere I go. You make money by coding a version of your product once and selling it a million times, not by selling your product and coding it a million times. my current employer still does not understand this concept yet either.|||I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?I realize that it doesn't help you much that your code relies on things that it shouldn't, and that is causing your problems. I think that we've all been there with code that was written long ago by folks that made assumptions that don't hold true with newer tools.
If your code relies on specific orders of attributes, and no gaps in the values in system tables (when your code shouldn't even know what those number are or that those gaps exist), then your code is faulty. You can work around the fault in your code by using the drop and create that Blindman suggested.
I'm sorry that you are stuck maintaining this code, but all we are trying to do is help... Getting hostile with us won't make your job any easier, and if you irritate the people that help you, that will probably make your job harder.
-PatP|||See, no one is helping me. That is why I'm getting a bit hot. Your wrote that my code is faulty. No it isn't. I'm under certain constraints of an application. One of them is my procedure being called by the application. Now suppose I do this drop/create deal. What if the table has ten million rows? That will take some time to run. Meanwhile, the user thinks that the app is hung up and does something stupid that maybe even implicit transactions may not be able to help?
Again, I have to work around what programmers have done, not them working around me. Just so everyone knows, I could note care less what order columns are in the table. The programmers, for whatever reason, do. If a table is created or altered outside of the order of the data dictionary table bad things happen. That IS NOT MY DESIGN.
I could create some rolling number sequence in SQL Server to get past this. My point is, why should I have to? I can't irritate people who help me because I've got no help. ya know? Everyone is talking like this is just normal. It just isn't.
Go ahead and lock this thread. It's going nowhere. Fast.|||Oh and before it is locked, if it is. Understand that my gripe isn't with this ordinal position as much as it makes me wonder what else I can't believe from the system. What else is bad? Is that so nuts of me?|||The system tables are correct, based on the rules for SQL Server. Please explain what you can't believe. I don't get what the problem is.
If your code isn't faulty, then it works "as is". If your code is faulty, then Blindman's suggestion will help you work around that fault. Again, I don't get what the problem is.
Plase help me to understand.
-PatP|||Why would you say the tables are correct? There is a gap in the ordinal value? What rules specifically are you speaking of. If I've got a table with ten columns, I add a column, it is eleven in ordinal position. If I drop it for the sake of doing it and re add it, it is still eleven in reality, not twelve as SQL Server will record it. Then on to higher numbers if I kept dropping it and re adding it. Now of course that isn't going to happen (continuous dropping/re add) but I'm just trying to get the point across.
Now in Oracle, I can do this all day long and user_tab_columns will show the correct column_id (1,2,3,4,5,6,7,8,9,10,11) no matter how many times I drop and re add a column. As I've stated before, I could easily create some rolling number while looking at syscolumns or something and just override what the ordinal value is in the table but I just don't like that. I'd rather do "fieldlocation+1<>colid" (note field location in my meta file begins with zero as opposed to 1 in syscolumns) to find something out of order, or a column that needs to be added. If I find something out of order, my procedure returns a message saying I wont do anything. In this case, manual intervention is required and that is how it should be. It is the application that will puke if columns are out of order, not my code. I really have to detect this though.
So, to sum it up, I'd love for colid to be in an order without gaps regardless of a column being dropped and then a new one added. That isn't right that SQL Server does anything else. And I'll say it again, what else is screwy if that is happening? Just like Blinds example about adding length to a column. My procedure does that, too. Now I can't expand a column correctly because maybe what is in syscolumns is actually wrong?
I'm not saying I can't get past this particular issue, I just wonder what else is wrong and I resent the fact I should have to jump through hoops to get a true sequential order from syscolumns.|||Being a user forum, we can really only deal with "what is", rather than "what should be". None of us can change the way syscolumns records column IDs. Perhaps you should take this to Microsoft, and see what they say?|||A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that.Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.|||I could poop in one hand and wish in the other to see which fills up faster! Those people at MS would be of no use. I'm sure they would offer a work around of "don't do that". I've found all kinds of Crazy things with SQL Server. Like looking at sysindexes in a particular database as another user in a different database. I get totally erratic results. That is just an example.
I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.|||Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.
Dude, get over yourself. I know that they are two different RDMS. I never said one set of code should run for both. I wrote that functionality that exists for one back end needs to exist for the other. That means two different procedures. However, I'd like to keep things simple. Thanks for the heads up though. And if the system tables are not intended to be used by anything other than the system, How should I determine a tables true structure? You can save your smart ass responses. They've been as little help as the rest of your post.|||I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.
Don't think so. You got upset because you define Oracle as sensible. I disagree. Cursors suck. Since oracle doesn't fit the SQL Server paradigm, Oracle must not be sensible, and a piece of c**p when it comes to the efficient use of limited system resources.
We agree to disagree!|||I never said one was perfect. Getting data from Oracle is like getting blood from a rock at times. Sometimes things are easy over "here" and hard "there" I never said one was better than the other. I mentioned Oracle as something to compare the situation with. Regardless, I've got to deal with things in both Oracle and SQL. It is just how things are.|||dba_one, we can all sympathize with your predicament, and i'm sure many of us can understand your frustration
however, at no point are you allowed to say things like "Dude, get over yourself" or "You can save your smart ass responses"
that's just not allowed|||Heck, that is tame but regardless, I didn't come here to fight. I didn't come here to have anyone just talk down to me, either. Regardless, I was looking for insight on a particular issue but there isn''t much that can be done about this particular thing so I've got to deal with it. So be it. I'm not mad, I'm just not the nice guy all the time. No harm intended and no harm done.|||OK.
Basically, the answer to your post boils down to:
A) The internals of SQL Server was not designed with your application in mind.
B) We can't change the internals of SQL Server.
C) No, not many people on this forum have run into the problem you are experiencing, because we have not designed applications such as yours.
D) We sympathize with your predicament.
Now lets all go back to playing nice.|||What the hell...fow did I miss all of this?|||Did you ever show us the code that is making these changes btw?
That might help out alot|||What the hell...fow did I miss all of this?
fow indeed!
:)
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)ooohhh dba-one, a padwon problem this is. meaningless the order of the data in the database is as Jedi Kaiser will readily remind you he will.|||Personally, some things are just not meant to be automated in my opinion
You'd have to post some code snipets though on what you are doing.
I use ERWin to do what you are doing, but I usually unload, drop and create the objects, then load|||Yoda say this ordinal problem not be a 0 to 1 issues. The voc file reads 0 for the first position while the system stuffs begins at 1. You didn't really think a Jedi master like me would be so stupid as to over look that did you?|||exec usp_madeof 'wcsub'
select colid,* from syscolumns where object_name(id)='wcsub' order by colid
Both will return the following in the results:
DOHFILING varchar 1 48
FORMTYPE varchar 2 53
(from sysobjects)
48 DOHFILING
53 FORMTYPE
Now, usp_made of is a procedure I wrote. It returns results in a fashion like Oracle's "desc" function. All that procedure does is read the INFORMATION_SCHEMA.[columns] view and order by ordinal position. Now, everytime I drop formtype and re add the column 53 will become 54 and so on. dohfiling will remain 48. This is nuts.|||Personally, some things are just not meant to be automated in my opinion
I very much agree. When I find columns out of order I refuse to automate the process. However, the columns are not out of order technically. I just can believe what syscolumns or other entries read. Even running alter table .. add column ... in the local DB as the owner is causing this so I can even remove my procedure from the matter.|||I tore out a couple hairs this week trying to get a Data Dictionary Collection script to work. Eventually, I just dumped the idea of checking columns by column ID and resorted to sorting the lower cased names of the columns and going by that. I figured if the column name exists, then I am OK. If it is not there at all, then I have to delete the column from the data dictionary. Or, if I find a column that is out of order, it must have been added, so in the data dictionary it goes. If I went by columnID I could be dropping good columns and re-adding them later on, when I find them again at the "end" of the table. Would that work for you?
EDIT: Bottom line is you have to work around the quirks in your RDBMS. And a non-contiguous set of column IDs counts as a quirk to me.|||Here read this.
http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
Once I read that, it only re-enforced dropping and re-creating the tables|||This has never happened and it is not an acceptable flaw of any RDBMS. Besides, my application expects things to be in a certain order and if it isn't, it will puke. I didn't design it that way but it is the way of things. If the table is created wrong then fine, manual intervention. If the system tables are wrong then how can I buy anything else they may tell me?|||This application is run all over the place at my clients sites. If it were an in house app I wouldn't mind dropping and recreating tables but that is horribly impractical in this case. Plus, I've had a few programmers here attempt to be clever and do things like that only to see it result in data loss. I can't attempt to automate the recreation of tables and moving data around. Too much risk for my taste.|||Personally, some things are just not meant to be automated in my opinionSo true. Experience is the difference between knowing what CAN be done, and what SHOULD be done. "Fail-safe systems fail by failing to fail safely."|||This application is run all over the place at my clients sites.
I don't understand, I would have a script that would run all the sql and apply the changes.
What kind of development lifecycle do you use?
And what's the frontt end written and how do you deploy that?|||The app is a VB/.Net app for insurance claims. It is a very complicated application as well. Scripts are fine in many cases but calls to my procedure are built in to it. When we make changes to table structures we simply send the meta data table. The app will then call my procedure to inspect and alter if needed, any involved tables. No single client has a standard version of our app because we will customize anything they want but the databases are largely the same with the exception of "user tables"
If we send an update that would say depend on a new table or two or many, we send the meta data file. The app will then test for existence and if they are not there my procedure creates the tables, index, keys, so on. Why send a meta data file and then have to manually do everything?
It seems everyone thinks I'm some kind of dummy for doing this! Our app has been doing things like this for nearly 20 years and the last 10 has been with Oracle or SQL Server depending on which DB the client wants to run it against. I just don't understand how this issue has only appeared now and not before.|||I can't be the only one who has run in to this can I?|||No single client has a standard version of our app because we will customize anything they want...and that is the root of the problem. Trying to be all things to all customers. I saw this as a problem back when I was a consumer of software, and I see it as a problem now that I am a producer of software. Client database get customized out the wazoo until they become administrative nightmares requiring the type of code you have had to implement (albeit for 20 years).
Software vendors should be experts in their industry and code their products according to best practices. Client modification of the database schema should not be allowed.
"You can customize it any way you want" is ultimately a flawed philosophy, and its reductio-ad-absurdum conclusion is an empty box with instructions to install the database server of the client's choice.|||You misunderstand. You may not tamper with the database but we will customize our programs. That is what I meant by that. Our Workers' Comp claim table is called WCCLM1 for example. Every client has this and if they get an update, they may need additional columns or columns expanded. if they add something to it, the app wont see it, etc
Oh and we don't ship a shrink wrapped product and tell them to plug it in to whatever they want. We support either Oracle or SQL Server only. We do a data conversion in most cases as well. We are hardly as stupid or sloppy as some may have understood.
Basically, client modifications to the database are not allowed and there is not a single thing that is arbitrary about the database design. Geez, all I need to do is add some columns or expand them without having to write a script every time. This isn't rocket science or reinvention of the wheel.|||Oh and one more thing. "You can customize it anyway you want" is true as long as you pay us to modify it. We don't ship code. We don't let clients ever have that, much less modify it. This isn't some open source trash. This is a 100k plus application.|||So you still run into the headache of maintaining code for as many versions as you have clients, but you have found a way to charge for the administrative hassle.
I'm not dissing your business plan. Whatever you and your clients agree to and makes you money is fair game. But I don't see how you can be surprised that rolling out updates in such a situation is a pain in the keister. It is difficult by design.|||Well, technically we only have two versions, 5.4 of our old system and 6.424 of out newer .NET version. Both use the same database, both use the same programs at any client site. What may be different is tables for a customized screen for a client claim program, Perhaps a custom AP, HR, etc interface. Our code and/or business model is not the mess some of you think it is. I guess I'm just not explaining it well.
Still, forget about all that. Even if it was a wreck, what does that have to do with what I consider a substantial flaw in this RDMS? Not a damn thing. The bottom line is that I should be able to add a column (oh and this happens even if I add the column manually and not via stored procedure in case anyone missed that) and have that recorded in the proper ordinal position in the system tables and views.
The feed back I'm getting here is that I'm stupid for expecting to actually utilize the alter table command. Someone explain why it's there if it can't be relied upon? You guys can talk all day long about how we distribute our app but no one can seem to offer anything of value with regard to the issue at hand.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!
Funny how I don't have this issue in Oracle. You don't even know what the hell you are talking about. Since when did I say what order they are in, inside the database matters to me? I never did. The application depends on things being a certain way. I didn't design that shit. Programmers did and now I have to deal with it. Still, how dare I expect things to be correct in SYSTEM TABLES! I'm not an idiot so I wish you people would stop assuming I am.
Thanks for nothing. All of you.|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.
I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?|||sweet Jesus the booty pleaser.
I bang this drum everywhere I go. You make money by coding a version of your product once and selling it a million times, not by selling your product and coding it a million times. my current employer still does not understand this concept yet either.|||I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?I realize that it doesn't help you much that your code relies on things that it shouldn't, and that is causing your problems. I think that we've all been there with code that was written long ago by folks that made assumptions that don't hold true with newer tools.
If your code relies on specific orders of attributes, and no gaps in the values in system tables (when your code shouldn't even know what those number are or that those gaps exist), then your code is faulty. You can work around the fault in your code by using the drop and create that Blindman suggested.
I'm sorry that you are stuck maintaining this code, but all we are trying to do is help... Getting hostile with us won't make your job any easier, and if you irritate the people that help you, that will probably make your job harder.
-PatP|||See, no one is helping me. That is why I'm getting a bit hot. Your wrote that my code is faulty. No it isn't. I'm under certain constraints of an application. One of them is my procedure being called by the application. Now suppose I do this drop/create deal. What if the table has ten million rows? That will take some time to run. Meanwhile, the user thinks that the app is hung up and does something stupid that maybe even implicit transactions may not be able to help?
Again, I have to work around what programmers have done, not them working around me. Just so everyone knows, I could note care less what order columns are in the table. The programmers, for whatever reason, do. If a table is created or altered outside of the order of the data dictionary table bad things happen. That IS NOT MY DESIGN.
I could create some rolling number sequence in SQL Server to get past this. My point is, why should I have to? I can't irritate people who help me because I've got no help. ya know? Everyone is talking like this is just normal. It just isn't.
Go ahead and lock this thread. It's going nowhere. Fast.|||Oh and before it is locked, if it is. Understand that my gripe isn't with this ordinal position as much as it makes me wonder what else I can't believe from the system. What else is bad? Is that so nuts of me?|||The system tables are correct, based on the rules for SQL Server. Please explain what you can't believe. I don't get what the problem is.
If your code isn't faulty, then it works "as is". If your code is faulty, then Blindman's suggestion will help you work around that fault. Again, I don't get what the problem is.
Plase help me to understand.
-PatP|||Why would you say the tables are correct? There is a gap in the ordinal value? What rules specifically are you speaking of. If I've got a table with ten columns, I add a column, it is eleven in ordinal position. If I drop it for the sake of doing it and re add it, it is still eleven in reality, not twelve as SQL Server will record it. Then on to higher numbers if I kept dropping it and re adding it. Now of course that isn't going to happen (continuous dropping/re add) but I'm just trying to get the point across.
Now in Oracle, I can do this all day long and user_tab_columns will show the correct column_id (1,2,3,4,5,6,7,8,9,10,11) no matter how many times I drop and re add a column. As I've stated before, I could easily create some rolling number while looking at syscolumns or something and just override what the ordinal value is in the table but I just don't like that. I'd rather do "fieldlocation+1<>colid" (note field location in my meta file begins with zero as opposed to 1 in syscolumns) to find something out of order, or a column that needs to be added. If I find something out of order, my procedure returns a message saying I wont do anything. In this case, manual intervention is required and that is how it should be. It is the application that will puke if columns are out of order, not my code. I really have to detect this though.
So, to sum it up, I'd love for colid to be in an order without gaps regardless of a column being dropped and then a new one added. That isn't right that SQL Server does anything else. And I'll say it again, what else is screwy if that is happening? Just like Blinds example about adding length to a column. My procedure does that, too. Now I can't expand a column correctly because maybe what is in syscolumns is actually wrong?
I'm not saying I can't get past this particular issue, I just wonder what else is wrong and I resent the fact I should have to jump through hoops to get a true sequential order from syscolumns.|||Being a user forum, we can really only deal with "what is", rather than "what should be". None of us can change the way syscolumns records column IDs. Perhaps you should take this to Microsoft, and see what they say?|||A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that.Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.|||I could poop in one hand and wish in the other to see which fills up faster! Those people at MS would be of no use. I'm sure they would offer a work around of "don't do that". I've found all kinds of Crazy things with SQL Server. Like looking at sysindexes in a particular database as another user in a different database. I get totally erratic results. That is just an example.
I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.|||Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.
Dude, get over yourself. I know that they are two different RDMS. I never said one set of code should run for both. I wrote that functionality that exists for one back end needs to exist for the other. That means two different procedures. However, I'd like to keep things simple. Thanks for the heads up though. And if the system tables are not intended to be used by anything other than the system, How should I determine a tables true structure? You can save your smart ass responses. They've been as little help as the rest of your post.|||I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.
Don't think so. You got upset because you define Oracle as sensible. I disagree. Cursors suck. Since oracle doesn't fit the SQL Server paradigm, Oracle must not be sensible, and a piece of c**p when it comes to the efficient use of limited system resources.
We agree to disagree!|||I never said one was perfect. Getting data from Oracle is like getting blood from a rock at times. Sometimes things are easy over "here" and hard "there" I never said one was better than the other. I mentioned Oracle as something to compare the situation with. Regardless, I've got to deal with things in both Oracle and SQL. It is just how things are.|||dba_one, we can all sympathize with your predicament, and i'm sure many of us can understand your frustration
however, at no point are you allowed to say things like "Dude, get over yourself" or "You can save your smart ass responses"
that's just not allowed|||Heck, that is tame but regardless, I didn't come here to fight. I didn't come here to have anyone just talk down to me, either. Regardless, I was looking for insight on a particular issue but there isn''t much that can be done about this particular thing so I've got to deal with it. So be it. I'm not mad, I'm just not the nice guy all the time. No harm intended and no harm done.|||OK.
Basically, the answer to your post boils down to:
A) The internals of SQL Server was not designed with your application in mind.
B) We can't change the internals of SQL Server.
C) No, not many people on this forum have run into the problem you are experiencing, because we have not designed applications such as yours.
D) We sympathize with your predicament.
Now lets all go back to playing nice.|||What the hell...fow did I miss all of this?|||Did you ever show us the code that is making these changes btw?
That might help out alot|||What the hell...fow did I miss all of this?
fow indeed!
:)
Wednesday, March 7, 2012
An attempt to attach an auto-named database for file
I build a WebApp which i use the default DataBase that come with the App_Data folder.
I have Users and roles into that folder with all my tables regarding my new App.
Why i cant make it work under IIS on my webserver ? When i open it with Visual Studio everything works but outside of it nothing.
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\Survey\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf;
I look arround and cant find a thing on how to fix my issue. I try deleting the Folder under my User Account for SQL Express and nothing happend.
Make sure your IIS account (ASPNET or NETWORK SERVICE) has read&write permission on the folder where the database file locates.|||Yes i did that ! even uers have modify to it. under that folder !|||Im still looking for help arround here !
anybody fix the issue?
|||It is not working because IIS is not the place to store database files, you store those in the data subfolder of Microsoft SQL Server in programs. You can modify the code in the thread below for your use. You may need to start with the trail version of SQL Server and later buy the developer edition it is under $40 on the web. Hope this helps.
http://forums.asp.net/thread/977493.aspx
Saturday, February 25, 2012
amount of Records SQL 2005 can handle
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John
|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>
|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John
|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>
|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?
amount of Records SQL 2005 can handle
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version o
f
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: [url]http://www.sqlhacks.com/faqs/simple_monitoring[/
url]
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version o
f
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: [url]http://www.sqlhacks.com/faqs/simple_monitoring[/
url]
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?
amount of Records SQL 2005 can handle
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>> Hello
>> As someone else on here has mentioned, run the SQL profiling tools,
>> SQL is very efficient at adding / view data. The problems start to
>> occur with large tables that have no indexes, requiring SQL server to
>> start scanning tables every time it requests a record.
>> Missing Indexes will exponentially slow down a database and so will
>> poorly written code that doesn't take advantage of SQL server
>> features.
>> The databases I work with are in the hundreds of Gigabytes, with
>> tables also in the 10's millions rows, constantly being added and the
>> performance is fast on very standard hardware (dual core, 4 gb ram
>> etc) - the only times I see SQL become very busy is when the code
>> talking to SQL is inefficient :-)
>> The actual limit for SQL 64 is so huge you won't get there in the next
>> 5 years :-) and if you search google for SQL VLDB information you can
>> find SQL scales to very large organisations / data warehouses.
>> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>> > Hi.
>> > What are the limitations of SQL 2005? I mean the number of tables,
>> > relationships, mappings within the relationships and number of
>> > records?
>> > we have a customer with 64 bit AMD server used as the CRM databse
>> > server.However, the database server is performing absolutely bad in
>> > the recent days.
>> > The CRM server is 32 bit.
>> > Questions:
>> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
>> > server entegration?
>> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
>> > 3- What are the limitations? I mean if there are about 300 tables,
>> > total of 900 relationships in the database and about 3 million
>> > records, would it do bad to the system?
>> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
>> > is used by SQL where the CPU is about 90%. the system is running at
>> > more than 60% CPU and a few times a day it reaches 100% and the system
>> > cannot work.
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>> Hello
>> As someone else on here has mentioned, run the SQL profiling tools,
>> SQL is very efficient at adding / view data. The problems start to
>> occur with large tables that have no indexes, requiring SQL server to
>> start scanning tables every time it requests a record.
>> Missing Indexes will exponentially slow down a database and so will
>> poorly written code that doesn't take advantage of SQL server
>> features.
>> The databases I work with are in the hundreds of Gigabytes, with
>> tables also in the 10's millions rows, constantly being added and the
>> performance is fast on very standard hardware (dual core, 4 gb ram
>> etc) - the only times I see SQL become very busy is when the code
>> talking to SQL is inefficient :-)
>> The actual limit for SQL 64 is so huge you won't get there in the next
>> 5 years :-) and if you search google for SQL VLDB information you can
>> find SQL scales to very large organisations / data warehouses.
>> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>> > Hi.
>> > What are the limitations of SQL 2005? I mean the number of tables,
>> > relationships, mappings within the relationships and number of
>> > records?
>> > we have a customer with 64 bit AMD server used as the CRM databse
>> > server.However, the database server is performing absolutely bad in
>> > the recent days.
>> > The CRM server is 32 bit.
>> > Questions:
>> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
>> > server entegration?
>> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
>> > 3- What are the limitations? I mean if there are about 300 tables,
>> > total of 900 relationships in the database and about 3 million
>> > records, would it do bad to the system?
>> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
>> > is used by SQL where the CPU is about 90%. the system is running at
>> > more than 60% CPU and a few times a day it reaches 100% and the system
>> > cannot work.
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?
Subscribe to:
Posts (Atom)