Log Sent Rate Is Low As Compared To Log Bytes Flushed/sec

Jun 12, 2007

Hi,

We have Asynchronous Database Mirroring on SQL Server 2005 SP2 Entprise Edition/Windows 2000 Advanced Server. We noticed that log sent rate is quite low (average 1.3 MB/sec) in most of the cases whereas "Log bytes flushed/sec" is high (1.4 MB/sec) as a result Log send queue keeps on increasing and finally taking all the transaction log space. Our disk queue length is always in range of 0.01. And prinicipal and mirror servers are on local LAN.

I tried on low end server and high end server and in both cases Log sent rate is approx 1.3 MB/sec (Maximum 4 MB/sec).

Is there any limitation on Log sent rate?

How can we improve on log sent rate? Since both servers are on local LAN, network bandwith does not seems to be an issue.

Any help is greatly appreciated.

Thanks,

Ramesh

View 2 Replies

When Is Procedure Cache Flushed Or Cleared?

Jul 23, 2005

Using SQL Server 2000. When does SQL flush or clear the procedurecache? I am dynamically creating and dropping stored procedures (SP).Does SQL clear the cache for the SP that has been dropped? If not,when the SP is recreated (with the same name), does SQL use theexecution plan from cache?Thank you in advance.Jack

View 2 Replies View Related

Sql 2000 Compared 2...

Apr 2, 2004

anyone know reasons why sql server 2000 is a better choose for db creation instead of say oracle and mc access???

THANK YOU

View 10 Replies View Related

SSIS Compared To DTS?

Dec 7, 2007

There is no doubt that SSIS has been designed to build on the lessons learned from DTS but in one respect I think that there may have been a backward step and that is ease of use. A big attraction (a hallmark even) of DTS was that you could configure a package to move data between data sources and destinations in minutes.

When you first start using SSIS this is far from being true. I tended to get a lot of errors (many of them not very helpful) relating to stored procedure parameters, truncation of data etc. Looking around this Forum I think that I am not alone. This is partly a learning curve issue but I think that the learning curve is much steeper for SSIS.
.
This is not a plea to go back to DTS, we must move on, but I would like to think that there will be an emphasis on improving ease of use for SSIS going forward.

For the record two general improvements that I can see in SSIS (over DTS) are:-

1. A more logical structure.
2. The ability to easily set any Package Property, not just Variables at run time.

View 7 Replies View Related

Two Strings To Be Compared Have Different Collation.

May 10, 2006

I've written a stored proc which passes in an SqlString parameter and compares it with an SqlString read from an SqlDataReader.

I get the following exception:

System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.

Any ideas on collation inside CLR stored procs?

Thanks.

View 3 Replies View Related

SQL 7 High Page Faults/sec Compared To 6.5 ?

Aug 11, 1999

I am running an application on one NT Server, running against SQL Server 6.5 sp 3, and SQL 7 with sp1 applied.

The application is a 'data migration' type application - ie heavy insert and update workload - against many (50+ tables) with many different SQL statements.

The SQL 7 server is configured with 'floating' memory.

On SQL 7 - I am experiencing very high page faults/second for the sqlservr process - sometimes peaking at over 1,000. I was under the impression any number greater than 10 indicates a problem with system performance.

The same application, same data, same NT configuration etc against SQL 6.5 does not page fault. SQL Server 6.5 completes the work faster than 7.

Could anyone help me understand what's going on ?

Thanks in advance.

View 2 Replies View Related

WHILE Loop Speed Compared To Cursors?

Jul 23, 2005

Working on some new code, I'm coming across WHILE loops used instead ofcursors. I was curious if anyone had any stats on how the speed ofdoing this compares to the speed of a cursor. I typically avoidcursors for performance sake, but I'm not sure how this avoids thespeed hit of a cursor, since it's doing essentially the same thing.Many thanks.

View 4 Replies View Related

Data Access Very Slow In .net As Compared To VB

Jan 9, 2006

Hi,

I have migrated my app from VB to VB.Net. A 3-tier app with remoting and COM+.

I am experiencing a long wait time of about 3 times higher than what it would take in the VB App.

I am using DataAdapter.FiLL method to fill the datatable.

I have tried.

Using DataReader ( Made the things worse )

Using BeginLoadData and EndLoadData

Creating a Dataset and calling fill with the dataset so that the round trip to the middletier is saved to bring the SQL.

But i feel now that whatever is done. the problem is with the fill method only?

Is there any alternative?

Please suggest. It is one of the most important thing which if not possible may lead to scrapping up idea of upgrading to .Net.

Shri

View 10 Replies View Related

Rate() And PMT()

Oct 13, 2004

If you are familiar with Crystal reports or Visual basic, you may be familiar with the Rate and Pmt functions.

I need to duplicate them in SQL sever 7.

Anybody have code for this already? I hate re-inventing the wheel.

More (Unnecesary) details:
I have a client who has handed me the formula that I need to use for calculating Interest rates. Unfortunatly, the formula was written in Crystal reports, so now I need to pick it apart and do the work that CR does automaticly. Any help?

View 1 Replies View Related

VERY Slow Generate Scripts On SQL 2005 Compared To

Jun 10, 2008

i was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.

average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.

can anyone tell why ? thx in advance

View 9 Replies View Related

MSSQL ODBC Difference Compared To MySQL

Mar 6, 2007

The source for this problem can be found http://www.wellytop.com/SQLProblem.zip

This test creates two threads each with a database connection and uses transactions to insert values into the same table.
The objective of this test is to check that a thread cannot read the results from a pending transaction on a different thread.
In effect this checks dirty reads do not happen and transaction locking.

The test runs correctly and displays "PASSED" with MySQL indicating the transaction and threading worked.
When running with MSSQL Express 2005 it reports a deadlock error during a transaction.
It's not really possible to re-run the transaction and I would like MS SQL to operate similar to MySQL, i.e. MySQL waits for the other transaction to finish before the next transaction can operate on those table rows. I'd like to use MSSQL but I am wondering why this error doesn't happen with MySQL and so have, for the moment, chosen to use it as my preferred database solution.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.

I've tested this with a fresh install of Windows XP SP2 and no firewall turned on.

To run this test with MSSQL Express2005 use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named MyExpressTest and attach this to an empty database that has been created with the default values. Enable the #define MSSQL in the coude otherwise it tests with MySQL.

To run this test with MySQL (to show how this test should work) use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named mySQLNewTest and attach this to an empty database that has been created with the default values. Comment out the #define MSSQL to switch to MySQL mode.

View 6 Replies View Related

At What Rate Is The Db Growing???

Oct 27, 1999

Hello,

Does 7.0 store db Growth rate information?

I am looking for information that tells me how fast a db is growing in MB and or percentages over a given period of time, ie weekly, monthly, yearly etc. Either in real numbers or estimates. Does 7.0 already store something like this or do I need to create some code for this?

Or does someone have something like this already coded that they would be willing to share?

Thank you in advance.
Troy

View 2 Replies View Related

VERY Slow Generate Scripts On SQL 2005 Compared To SQL 2000

Aug 1, 2007

View 2 Replies View Related

Transact SQL :: Text Data Type Cannot Be Compared With Distinct

Oct 9, 2015

Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue.

But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2

The text data type cannot be selected as DISTINCT because it is not comparable.

DECLARE @c NVARCHAR(MAX)
WITH c1 AS (
SELECT [abcd] AS table_name
FROM [intranet].[dbo].[soccerfieldinfo]
where [abcd] IS NOT NULL
), c2 AS (
SELECT Row_Number() OVER (ORDER BY table_name) AS r

[Code] ....

View 3 Replies View Related

Tax Rate Data Type

Jul 15, 2004

Hi,
I want to store tax rate in my tables. I set the data type to float, I wan't 4 decimal places and the data in the table has 4 decimals, but when I run a query in query analyzer it returns: 4.4999999999999998E-2 instead of 0.045.
How can I fix this?

View 4 Replies View Related

Hourly Rate For Consulting

May 8, 2008

I am working with a company that needs some SQL Reporting developed. What is the going rate for SQL Reporting work?

View 14 Replies View Related

Date Between Query - How To Get Rate Value

May 29, 2014

I have a Table as below

Month Year Rate
1 2013 100
2 2013 101
8 2014 105

The rate is the value for the Month(1)January and year 2013.

I wana have a query which need to get rate value between 3rd month 2013 and 5the month 2014.

The Output has to be

3 2013 101
4 2013 101
5 2013 101

to

4 2014 105
5 2014 105

View 2 Replies View Related

Internal Rate Of Return

Jan 25, 2003

anyone wrote a function to calcuate internal rate of return?

View 8 Replies View Related

Procedure Cache Hit Rate

Feb 9, 2007

I have installed a SQL Server diagnose tool for evaluation. It prompts and warns me that "Procedure Cache hit rate is for example 15%. Its help indicates:

The Procedure Cache Hit Rate alarm is raised when the ratio between the number of times SQL Server looks for a plan in the procedure cache and the number of times it does not find a required plan in the procedure cache falls below a threshold.

A low procedure cache hit rate indicates that SQL Server is finding fewer of the query execution plans it needs already in memory and therefore has to perform more compiles. These extra compilations will degrade SQL Server performance by causing extra CPU load.

What can I do to increase the rate?

Canada DBA

View 2 Replies View Related

Stored Procedures On SQL 2000 Compared To Dotnet's Datatable.select

Jul 20, 2005

Hi,Has any one ever compared the performance of calling a DataTable'sSelect method with a stored procedure doing the same thing?My point is:dataRows = DataTable.Select(filter) is better orPassing paramters to stored procedure?The datatable holds about 500-700 rows at any given time.If I select one of the approaches the business logic will go intorespective layers.With dotnet in picture what would be a good approach- Have the data in Datatable and do a filter on the data or callstored procedures which has been the convention.Can some one pl. suggest?

View 3 Replies View Related

Adding Logins Via SSEUtil Compared To SQL Server Management Studio

Feb 7, 2007

Hello all,

I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:

USE [DBName]
GO

EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO

USE [master]
GO

EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO

CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [DBName]
GO

EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO

ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO

REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO

EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO

EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO

Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.

Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAMEInstance -run USERS.SQL), it processes everything, except the Logins.

This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.

Am I on the right track? Or is there another way to automate the adding of Logins?

Thanks in advance,

DSXC

View 5 Replies View Related

Data Transfer From Lotus Notes Very Slow Compared To SQL 2000 DTS

Sep 7, 2006

To extract data from an ODBC source, try the following:

Add an ADO.Net Connection Manager.
Edit the Connection Manager editor and select the ODBC Data Provider
Configure the Connection Manager to use your DSN or connection string
Add a Data Flow Task to your package.
Add a Data Reader Source adapter to your data flow
Edit the Data Reader source adapter to use the ADO.Net connection manager that you added.
Edit the Data Reader source to query for the data you wish to extract.

hth

Donald

Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.

When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.

Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?

Thanks!

View 3 Replies View Related

Power Pivot :: Find Percent Of One Of Last Occurrence Compared To All Last Occurrences (DAX)

Jun 3, 2015

I have a fact -REVENUE table of accounts, each account can have multiple instances . And I have an Aggregate that summarize the latest occurrence of a revenue for each account ( in a chosen Period ) :

AccountLastRevenue:=SUMX(
VALUES('scd_FactAccountRevenue'[Account]),
CALCULATE(
SUM('scd_FactAccountRevenue'[Revenue]),
LASTDATE('scd_FactAccountRevenue'[Revenue_Date]) )
)

How can I find the percent of one of the lastest accounts Compared to all accounts? Assuming i have connect Dim_Time ( Y-Q-M-D ) to Revenue_Date, how can i find the percent of one Month Compared to all the months in the Quarter ( And so on hierarchies ) ?

scd_FactAccountRevenue:

powerpivot :

View 11 Replies View Related

Calculate Average Growth Rate

Apr 30, 2008

I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.

Now, I'm trying to figure out what the fastest growers are. Here's the table ddl

CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.

I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.

what am I missing here?

View 10 Replies View Related

Setup Rate Table For SELECT BETWEEN

Mar 26, 2004

Have the following rate schedule - amount and discount rate

$0-10 = 1% discount
$10-100 = 2%
$100-over = 3%

I created the following rate table with min range and no max range so last entry will handle everything over 100$
$0 1%
$10 2%
$100 3%

I then create a view to translate it as min and max range
$0 10 1%
$10 100 2%
$100 max 3%

Select* from view where $10 between minrange and maxrange
give 2 rows
1%
2%

Question: Is there a way to structure the rate table so I can use SELECT BETWEEN and
what is the most common way to setup a rate table

View 3 Replies View Related

Pickup Tax Rate Without Using CROSS JOIN

Aug 29, 2005

I need to pickup a tax rate, that is stored on a 1 record file. I would like to avoid using the CROSS JOIN. Is there a way to SELECT the record and set a Variable = to the tax rate so I can pickup the rate in another SELECT statement on each record?

View 10 Replies View Related

TempDB Is Growing At An Alarming Rate!

Oct 16, 2007

Hi

We are having problems with our SQL server 2000.
The problem is that on a daily basis we run out of disk space and I always have to run shrinkdatabase on tempdb.
Today we started with 160GB of free space and by the end of the day it was gone!

Yes we do have many jobs running on our SQL server pulling data in from many sources. But I dont know how to find out which job is causing this problem. I have a suspicion that it could be a job that runs hourly that pulls data from Oracle (approximately 10000 rows each time), but that job has been active since the 28th August 2007. We only started running out of space in the past 5 days. Any suggestions would be appreciated as to what is causing this or how to diagnose the problem.

Thanks

View 14 Replies View Related

Procedure Cache Hit Rate Is Low - Need Advice

Mar 18, 2008

I have a 32 bit SQL 2005 EE clustered installation with 10GB of physical memory and AWE enabled. Our monitoring tool, Spotlight, is reporting the Procedure Cache to be 384MB and a Hit Rate of 75% on a fairly regular basis. Sometimes the Procedure Cache increases to 495MB and a Hit Rate of 82%.

(1) With 2005 can the Procedure Cache be increased?

(2) What is the max size of Procedure Cache?

(3) How do I increase the Hit Rate to a higher percentage?

I do not encounter the issue on any other SQL Server installation, however this is our only cluster.

DBCC PROCCACHE
num proc buffs = 64889
num proc buffs used = 1135
num proc buffs = 1135
active proc cache size = 2896
proc cache used = 364
proc cache active = 364

Thanks, Dave

View 1 Replies View Related

How Can We Improve The Cover Rate Of The Model?

Apr 21, 2006

Hi, all here, I found that in my case when I trained the data mining models, the model cover rate is very low (in my case, the train data set has 82 rows but the case occuring in the models I trained is only 25). How can I improve the cover rate to improve the quality of the models? (if it is possible in SQL Server 2005) I am using SQL Server 2005.

Cheers.

View 11 Replies View Related

ADO.NET Returns Different Colum Value When Compared To View Results In SQL 2005 Management Studio

Feb 7, 2007

I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.

View 2 Replies View Related

Report Running Very Slow Compared To Query Analyzer - High TimeDataRetrieval

Jun 26, 2007

Hi,

I have a report in SQL Reporting Services 2005 which calls a stored proc and the report takes a very long time to run and sometimes returns zero records. But when i run the stored proc in query analyzer it takes about 4 seconds!!

I have checked the execution log on the RS using the below sql:

Code Snippet

use ReportServer

Select * from ExecutionLog with (nolock) order by TimeStart DESC

It shows that i have a large amount of time for the dataretrieval (601309ms, about 10mins) and does not return any records most likely because of a query timeout:

TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
601309 2227 3 1 rsSuccess 4916 0

The weird thing is that when i run it in query analyzer, i get about 400 records in 4 seconds !!

I dont understand what RS is doing to take up so much time like this to retrieve data.

The report is very simple - it basically returns the records straight out into a table.

The only thing I somewhat suspected was a parameter data type conflict between RS and SQL, specifically dates. I have a start and end date parameter in the report - i tried specifying this as date and string to see if it made any difference but it didn't.

Any help would be greatly appreciated.

View 19 Replies View Related

Pickup Rate Based On Effective Date

Oct 16, 2005

I have a procedure that requires picking up the Rate based on Effective Date. This is what I have so far:

SELECT
SHPD.ProductID,
SHPD.ReceivedDate,
SHPD.Shipper,
SHIP.UnitRate
FROM tblShipmentDet SHPD
LEFT OUTER JOIN tblShippers ON SHIP.ProductID = SHPD.ProductID AND SHIP.Shipper = SHPD.Shipper AND Max???(SHIP.Effectivedate) <= SHPD.ReceivedDate

Because there can be more than 1 Shipper record, I would somehow need to pickup the Maximum EffectiveDate in each case. I realize I cannot use the Max aggregate in the JOIN. Not sure where to go from here. On the Mainframe I used a LOOKUP function that would return the correct EffectiveDate. Help would be appreciated.