I'm looking for a source of performance information for SQL Server (Express, though I assume it's the same).
I need to know how going from 1,000 x 1000 byte records to 100,000 x 1000 byte records will affect CRUD response times.
I recognize there's always the "it depends"; I'm hoping there are some sources of baseline performance metrics out there that I can extropolate from.
Any guidance on this would be appreciated.
Thanks!
I have been asked to perform a performance stress test on a SQL server with new hardware that we are going to be receiving.
How have some of you performed your stress analysis against new or existing hardware?
This hardware that I am going to receive will have to be configured within a high availabilty environment. I want to take this opportunity to really put a beat down on this server.
Thank you all for your suggestions.
SQL 6.5 had counters for monitoring SQL licenses. Where did Microsoft hide them in SQL 7.0? Or did they just disappear? If they are gone, is anyone doing anything to monitor the licenses with something like perfmon? User Connections doesn't help because the application takes several and not always the same number of user connections for one license.
View 2 Replies View RelatedWe are setting up a test lab environment with 100 machines. We want one master testing db that gets replicated to each to run scripted application tests nightly.
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
hi every one,
i need to test SSIS pacakge which will import data from different database where record count is around 5 millions.
iam planning to test it through c# code as well as manually also.
SSIS source : consist of 7 tables
SSIS destination :consist of 7 tables
Using c# code iam trying to run ssis package through batch file.
i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept.
am i going right way ,can any one suggest best and productive way to test the ssis package .
what are the other things i need to test it.
do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination.
Regards
Arif shareef
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies View Related1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedHello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedI am trying to test to see if my code is returning rows. If it's not I want to display an error saying "Nothing Found" Please review and give me your thoughts on the best way to accomplish this.
1 Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
2 Dim SubEmail As String
3 Dim SubPassword As String
4 SubEmail = txtNewsEmails.Text
5 SubPassword = txtNewsPassword.Text
6 Session("NewsEmail") = SubEmail
7 Session("NewsPassword") = SubPassword
8 Dim sID As Integer
9
10 Dim cs As String = ConfigurationManager.ConnectionStrings("csTiPs3").ConnectionString
11 Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(cs)
12 cn.Open()
13 Dim selectString As String = "Select SubscriberID from NewsletterSubscribers WHERE SubscriberEmail = '" + SubEmail + "' AND SubscriberPassword = '" + SubPassword + "'"
14
15 Dim cmd As SqlClient.SqlCommand = New SqlCommand(selectString, cn)
16
17 Dim reader As SqlDataReader
18 reader = cmd.ExecuteReader
19 While reader.Read()
20 sID = reader("SubscriberID")
21 End While
22 Session("SubscriberID") = sID
23
24 reader.Close()
25
26 rtsNewsletters.SelectedIndex = 1
27 rtsNewsletters.FindTabByText("Subscribe").Enabled = True
28 rmpNewsletters.SelectedIndex = 1
29
30 End Sub
Thanks all,
Brett
test
View 9 Replies View Relatedtest
Duplicate post
View 2 Replies View RelatedHi all,
I've inherited the administration of two SQL Servers one 7.0 and the other 2000. We are looking into upgrading to 2005 so I'm gathering all the info I can on the DBs and applications using them.
Now, how can I check if a DB is currently in use? Is there a way to find out when a DB was last accessed?
By the way, I've never done DB administration so you can figure out how desperate I'm getting.
Thanks all for your help,
GS
pratap writes " Dear respected sir,
i need small information regarding how the picture image data can be stored in the database and how it can be made possible. is there for the need for any conversion.
thank you
awaiting for your reply"
Hi, I hope someone can help me with this.
I want to get a query from a database
from between '3' AND '5' (NO PROBLEM)
but I want to add that I want info from 7 and up
how would I write that ? (if I do it in seperate lines the query returns nothing) which is not true.
Thank you.
Well i need to document what tables i have in my stored procedures. Im really new to sql as well. And the problem is if i try code i have or sp_depends for instance it will only tell me the tables that are in that DB so if I have multiple tables from different DB's they are left out. I was told that sp_MSForEachDb for go through each DB and I could try it that way. Any help or example code dumbed down for me would be awesome. This was posted in a previous thread but i though this would be a better explanation
View 3 Replies View RelatedI recently lost my job and wanted to do some test development to keep my skills up to date. The problem is I don't have access to any data sources. Is there such a thing around? I tried installing microsoft's trial of SQL 2005 but can't get it to run on my laptop and SQL 2000 trial no longer exists. I simply need to create a SQL db/tables etc. Thanks in advance.
View 9 Replies View RelatedHi!
I am currently utilizing the checksum function to generate a hash that I later compare to detect changes in a row.
CHECKSUM(field1, field2, field3, field4)
Now I'd like to use the HashBytes function instead over the same fields. But the HashBytes function accepts only one data value. What is the most effective and reliable way of getting an MD5 over several fields?
Thanks
Hi.
I have an application where I allow users to type in their SQL queries. Before I store those queries I have to make sure that they are correct, both syntax-wise and data type-wise. For that I execute the query against the database and trap any errors that may be returned and that's how I judge if the query was OK.
In order to keep this test as quick as possible, I tried to add a WHERE clause to it like: WHERE 1=2, so no results are returned. But then I discovered that the addition keeps errors from happening if they are of ata type nature.For instance "select orderid + 'test' from orders where 1=2", run against the Northwind database, returns no errors, while OrderID is numeric and U'm adding a string to it!
Next, I tried to return only one row: "select top 1 orderid + 'test' from orders". This time the error is thrown, however the query still takes a looong time when run on a huge table. I don't kknow why that is, but it seems that the engine runs the query for the entire table and then gets the first row!
Does anyone have an idea ehat's happening or have a better suggestion on how I can perform my test without killing the database?
Thanks.
I have a stored procedure which runs a query, then turns it on its side (so that the rows are column headers).What I need to do now is test if any of the values in a row from this stored procedure are null.Any suggestions on how to get started? Is there some function that will tell me if any value in the row is null?
View 2 Replies View RelatedI need to refresh a test db that is running on the same instance as the prod db. Should I just use DTS or is there a better way?
View 3 Replies View RelatedHello Everyone,
What is the best way to test connectivity with the database from the application on a frequest basis?
Thanks.
Hi -
I'm having a problem with a VB6 program I'm writing that posts data to a SQL server. The problem occurs when the SQL server is unavailable. When this happens it causes the program to hang for at least 30 seconds before releasing. The hang happens at the code that connects to the SQL server, because the server isn't there. I've added code found on this site to test the connectivity prior to making my actual data connection, but this causes the same hang. Is there any way to verify connectivity to an SQL server without actually trying to make the connection? I'm really trying to eliminate the hang that occurs when the server is unavailable.
Thanks
I have gotten some criticism from coworkers regarding this test and just wanted to see what you guys think. I realize the wording could use improvement and any criticism towards making it easier to understand is much appreciated.FWIW - I had to solve this problem on the job so I feel it is a real-world test that helps me understand how people think and if they try to find alternate solutions.Thanks!~~~~~~~~~~~~~~~~~~~~Given a table that has over 100,000 records…SUBSIDIARY=========PARENT_IDINTCHILD_IDINTULTIMATE_PARENT_IDINTCLEANUP_INDBIT…where each PARENT_ID can have multiple CHILD_ID values, but the PARENT_ID should not equal the CHILD_ID. After an initial data load, the ULTIMATE_PARENT_ID and CLEANUP_IND columns contain NULL values (see page 2 for sample data).ULTIMATE_PARENT_ID is defined as the topmost parent in the chain for the particular CHILD_ID record, so if the chain was only 2-level’s deep the ULTIMATE_PARENT_ID is the CHILD_ID’s PARENT_ID’s PARENT_ID.Please write an answer for all three questions below:A)Which of the following queries should you run first?B)Write an optimized query to identify the ULTIMATE_PARENT_ID for each CHILD_ID and set its value into the ULTIMATE_PARENT_ID column.C)Write a query to identify ALL of the circular references and mark each record that is a circular reference by updating the CLEANUP_IND column to 1.~~~~~~~~~ Page 2 ~~~~~~~~~ Sample Data, remember though this table has over 100,000 records and the parent-child chain can go n-levels deep – where n is not known.PARENT_IDCHILD_IDULTIMATE_PARENT_IDCLEANUP_IND1024512NULLNULL362300NULLNULL887541NULLNULL10221024NULLNULL546887NULLNULL5122305NULLNULL112967NULLNULL697123NULLNULL901452NULLNULL2300666NULLNULL334445NULLNULL512903NULLNULL884554NULLNULL313313NULLNULL554884NULLNULL112119NULLNULL967555NULLNULL2305333NULLNULL33336NULLNULL541546NULLNULL10301020NULLNULL112999NULLNULL
View 14 Replies View RelatedThis is a test post
View 7 Replies View RelatedHi,
Whats the best way to test scalability on a database? I'm working on a new app, and have handed off developing the database to someone with more experience. Some tables will grow to many million records, and I don't want it to bottleneck. I need to have it fully tested before it goes live.
Is it reasonable to ask the person helping me to fill it with 10's of millions of rows to test performance? Is this a decent solution? If so, what would the best way to fill it be? If not, what steps should I take?
Thanks very much!
mike123
HI... i need test papers on SQL DB. multiple choice with 3 levelsof difficulty: beginner, intermediate and advanced. can anyone tell mewhere i can get them made? or anyone who can help me with this? anysites... any info will be helpful. thanks in advance.
View 1 Replies View RelatedCan anyone tell me how to find out if a .sdf file is encrypted? Im using C# and would like to know if the db that I am trying to open is encrypted. I would then like to attempt to open the db.
Any help would be appreciated!
hi all:
do ms sql 2005 have chi-square test?
sincerly!
How can I test sql codes and don't want to connect to company database?
My company is using sql server 2000.
Below is the test codes which create a simple table, calculate and just want to check before
writing more complicated codes.
Thanks
Daniel Ku
-------------------------------------------------------------------------------------------------------------------------------
create table EquipmentUptime (
equipmentId int not null
,transactionId int not null
,dateOccured datetime not null
)
go
--
insert into EquipmentUptime values (1,200,'01/01/2007')
insert into EquipmentUptime values (2,200,'01/01/2007')
insert into EquipmentUptime values (3,200,'01/01/2007')
insert into EquipmentUptime values (2,100,'02/12/2007')
insert into EquipmentUptime values (1,100,'02/25/2007')
insert into EquipmentUptime values (3,100,'03/10/2007')
insert into EquipmentUptime values (1,200,'03/14/2007')
go
--
select
equipmentId
,100*(cast((cast(sum(UpDays)as decimal(6,3))/cast(365 as decimal(6,3))) as decimal(4,3))) 'PctUpTime'
from
(
select
c.equipmentId
,datediff(dd,c.[UpDate],c.DownDate) 'UpDays'
from
(
select
a.equipmentId
,a.dateOccured as 'UpDate'
,coalesce(
(select top 1
b.dateOccured
from
EquipmentUptime b
where
transactionId=100
and b.equipmentId=a.equipmentId
and b.dateOccured >= a.dateOccured
order by
b.dateOccured asc
),'01/01/2008') as 'DownDate'
from
EquipmentUptime a
where
a.transactionId=200
) c
) d
group by
d.equipmentId