If the computer has ONLY 8 GB of physical memory available to it and you are allowing SQLServer to use over 214,000 GB of physical memory, where is it coming from?
Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for the OS and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server.
What is the physical memory? 8 GB
How much does the OS need? Let's say 2 GB
How much is left for SQL? 6 GB
Set your SQLServer maximum memory to a more reasonable value and then report what happens.
I will note that I have tested an application that was running 100 TPS (Transactions Per second) sustained on 32 bit machines where every transaction was hitting the database multiple times on a network with multiple client machines, a single app server and a single database server (again 32 bit with 2 CPUs.)
And I was never able to get the CPU on the database server machine to go above about 3%. The database was never even close to being a bottleneck.
I seriously doubt that 40 human users doing normal click through work would even be able to touch a 64 bit machine on even a adequately designed system.
Normal performance problems are as follows from most to least
1. Requirements (most impact)
4. Technology (network, language, OS, etc.) (least impact)
Now as per the other threads very badly configuring a server could have an impact. But so can 1-3 in the above.
If it is the database server then reinstalling and accepting all of the defaults would return it to the base line and that would be more than adequate for very long time even with a substantial increase in human users. If of course 1-3 are done right.
Try using datetime data type, BETWEEN may be useful to you.
This seems to indicate you are storing your dates as varchar -
ORDERBYCONVERT(DATETIME, follow_up_date) DESC
one of the most basic and expensive errors a database designer can make.
I don't think you can blame the database server if this is what you are asking it to deal with. When you have cleaned up the errors you should then use profiler to determine if indexes can improve the performance.
Never underestimate the power of human stupidity
I'm strugling with a string function.
I have a PRODUCT field and it contains 4 kind of values:
I want the values to look like this:
 if the length of PRODUCT without zeros is smaller then
7 the output is:
 if the length of PRODUCT without zeros is equal to 7 then output is:
 If the lengt of PRODUCT without zeros is equal to 9 then output is:
The If statements  and  are taken care of with af function that I call in my statement:
ALTER FUNCTION [dbo].[LPAD]
-- Add the parameters for the function here
-- Test with data as nvarchar like in Staging
@SourceString nvarchar(MAX), --Varchar(MAX),
RETURNS nvarchar(MAX) --Varchar(MAX) --<Function_Data_Type, ,Int>
RETURN --<@ResultVar, sysname, @Result>
(Select REPLICATE(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)
, CAST(RTRIM(dbo.LPAD(SUBSTRING(dbo.SD.PRODUCT, PATINDEX('%[^0]%',dbo.SD.PRODUCT+ ''), LEN(dbo.SD.PRODUCT)), 7, 0))
ASnvarchar(255)) AS PRODUCT
So if PRODUCT is smaller then 7 chars I need a zero to make a maximum length of 7 chars. The first 11 zero's need to dissapear and thats taken care of with the above function and statement.
It's the third statement that gives me a headache, I also want the PRODUCT with 9 chars without any leading zero.
Does anyone know how I can integrate the 3rd if please ???
This one will work assuming the product codes are always numeric:
DECLARE@INPUTNVARCHAR(MAX) = 'your product code with or without leading zeros goes here'DECLARE@TMP_INPUTINT = CAST(@INPUTASINT)
SELECTCASEWHEN LEN(CAST(@TMP_INPUTASNVARCHAR)) < 7THEN SUBSTRING(@INPUT, 1, 7)
My advice is free, and you may get what you paid for.
STUDENTNO TOTALSCORE ENGLISH GEOGRAPHY MATHEMATICS BIOLOGY C.
20 240 50 60 70 60
now i wish to retrieve records in the format:
SUBJECTNAME Exam I Exam II Exam III Avg
ENGLISH 50 78 67
GEOGRAPHY 60 67
MATHEMATICS 70 56
BIOLOGY 60 90
Any idea on i can go about this?. any help will be appreciated
Unless you have omitted in your post something from your database-model, my first suggestion would be to rework your database-model a bit. You're missing some stuff there which would make a lot of sense:
Then you can get the marks with a query like this:
FROM Student AS St
JOIN Mark AS Mk ON St.StudentId = Mk.StudentId
JOIN Exam AS Ex ON Mk.ExamId = Ex.ExamId
JOIN ExamType AS Et ON Ex.ExamTypeId = Et.ExamTypeId
JOIN Subject AS Sj ON Ex.SubjectId = Sj.SubjectId
StudentId = ... /* however you want to narrow your query result */
ExamId = ...
SubjectId = ...
Edit: What you can read when following the link Peter Loew has posted in his answer is basically the reasoning for my suggested rework of your database-model.
I have a query and it takes too much time to fetch
How much time would be allowed?
There's no faster alternative to splitting a string, but it does count as a design-mistake. Each attribute in the tupel should be atomic. You should not need to separate the facts from a single field. They should have been two separate columns.
You "could" try to create an view that does the splitting, and making sure that those calculated columns are materialized. It would effectively move the extra processing required to when the view is created, rather then when the data is requested.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
I have an SSRS report which has two columns X and Y value but it is same category, so for example, Axis is the main column under this I have X and Y values, for showing it in more sensible way I want to have a a row in the header which has Axis and from the next row I want to have X, Y headers under these I want to display V values and Y Values, I could have done same by taking two columns and keeping the X Axis and Y-Axis as column headers but I want to show them in more readable format.
Can I do it by using SSRS, please help me but any suggestion, link or code snippet, it would be great help for me.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
Last Visit: 31-Dec-99 18:00 Last Update: 4-Oct-23 0:38